Begin
/* material */
FOR
SELECT
a.productiontask_id as productiontask_id,
Case
When cast(
(
Select
Sum(cast(xT.MaterialQuantity*xs.LocalTAmount as numeric(13,4))/Cast(xs.Quantity as numeric(13,4)))
from
productiontasks2 xT
join Storedocuments2 xS On xS.id=xT.MaterialRow_ID
where
xT.ProductRow_ID = A.ID)
as numeric(15,6)) > 0 then
cast((
cast((a.LocalTAmount - AC.othercostamount-AC.TransportationAmount-AC.SpendingTaxAmount-AC.CustomsAmount) as numeric(13,2))/
cast((
Select
Sum(cast(xT.MaterialQuantity*xs.LocalTAmount as numeric(13,4))/Cast(xs.Quantity as numeric(13,4)))
from
productiontasks2 xT
join Storedocuments2 xS On xS.id=xT.MaterialRow_ID where xT.ProductRow_ID = A.ID)
as numeric(15,6)))
as numeric(15,4)) *
cast(cast(T2.MaterialQuantity*M.LocalTAmount as numeric(13,4))/cast(M.Quantity as numeric(13,4)) as numeric(15,4))
Else 0
End 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,
M.storecard_ID as INPUTSTORECARD_ID,
a.storecard_id as OUTPUTSTORECARD_ID,
'' as WORKER_ID,
'' as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 38 and leftside_id = SD.ID) = 0 then 0 else 1 end as ACC_RECORD,
SD.Firm_ID as Firm_ID,
SD.ID as Source_ID,
JO.MaterialInIncome as MaterialInIncome,
0 as Acc_Date,
SD.DocDate$DATE as Doc_Date,
T2.ObjVersion as ObjVersion,
SD.DocQueue_ID as SourceDocQueue_ID,
SD.Period_ID as SourcePeriod_ID,
SD.OrdNumber as SourceOrdNumber
FROM ProductionTasks2 T2
join Storedocuments2 M On T2.MaterialRow_ID =M.id
join Storedocuments2 A On a.ID = T2.ProductRow_ID
join Storedocuments SD ON SD.id = A.Parent_ID
join additionalcosts AC On AC.id = a.AdditionalCosts_ID
join plmjoborders JO On JO.productiontask_id = T2.parent_id
join Periods PS On PS.ID = SD.Period_ID
join DocQueues QS On QS.ID = SD.DocQueue_ID
WHERE (SD.DocumentType = '28') and (SD.DocDate$DATE >= :ADateFrom) and JO.Balanced = 'N' and JO.ID = :AJobOrderID
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_ID,
mInIncomeC,
Out_Acc_Date,
Out_Doc_Date,
Out_ObjVersion,
Out_SourceDocQueue_ID,
Out_SourcePeriod_ID,
Out_SourceOrdNumber
do
begin
Out_ValueType = 'VMV';
If ((mInIncomeC = 'A') Or (AAcceptJOSetting = 0)) then
begin
Suspend;
end
end
/* Pracovni listky - spravni rezie */
FOR
Select
a.productiontask_id as productiontask_id,
Case
when (select sum(SalaryCosts) from PLMOperations where jobordersroutines_id=R.ID) < 0.01 then 0
Else
cast(
(cast(o.SalaryCosts as numeric(15,5))/
Cast((select sum(SalaryCosts) from PLMOperations where jobordersroutines_id=R.ID)as numeric(15,5))
) as numeric(18,8))
* Cast(rad.GeneralExpense as numeric(15,5))
End 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,
N.storecard_id as OUTPUTSTORECARD_ID,
O.PerformedBy_ID as WORKER_ID,
O.WorkPlace_ID as WORKPLACE_ID,
Case
When (select count(ID) from Relations where rel_def = 38 and leftside_id = SD.ID) = 0 then 0
else 1
end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
SD.ID as Source_ID,
JO.GeneralExpenseInIncome as GeneralExpenseInIncome,
0 as Acc_Date,
SD.DocDate$DATE as Doc_Date,
O.ObjVersion as ObjVersion,
SD.DocQueue_ID as SourceDocQueue_ID,
SD.Period_ID as SourcePeriod_ID,
SD.OrdNumber as SourceOrdNumber
from
PLMoperations O
join PLMJobOrdersRoutines R On O.jobordersroutines_id = R.ID
join plmjobordersroutinead rad ON R.ID = rad.parent_id
join Storedocuments2 a ON a.AdditionalCosts_ID = rad.AdditionalCosts_ID
join Storedocuments SD ON SD.id = A.Parent_ID
join plmjoborders JO On JO.productiontask_id = a.productiontask_id
join PLMJOOUTPUTITEMS OI On OI.ID = R.parent_id
join PLMJONodes N On N.id = OI.owner_id
join Periods PS On PS.ID = SD.Period_ID
join DocQueues QS On QS.ID = SD.DocQueue_ID
WHERE (SD.DocumentType = '28') and (SD.DocDate$DATE >= :ADateFrom) and JO.Balanced = 'N' and JO.ID = :AJobOrderID
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_ID,
mInIncomeC,
Out_Acc_Date,
Out_Doc_Date,
Out_ObjVersion,
Out_SourceDocQueue_ID,
Out_SourcePeriod_ID,
Out_SourceOrdNumber
do
begin
Out_ValueType = 'SPR';
If ((mInIncomeC = 'A') Or (AAcceptJOSetting = 0)) then
begin
Suspend;
end
end
/* Pracovni listky - vyrobni rezie */
FOR
Select
a.productiontask_id as productiontask_id,
Case
when (select sum(SalaryCosts) from PLMOperations where jobordersroutines_id=R.ID) < 0.01 then 0
Else
cast(
(cast(o.SalaryCosts as numeric(15,5))/
Cast((select sum(SalaryCosts) from PLMOperations where jobordersroutines_id=R.ID)as numeric(15,5))
) as numeric(18,8))
* Cast(rad.OverheadCosts as numeric(15,5))
End 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,
N.storecard_id as OUTPUTSTORECARD_ID,
O.PerformedBy_ID as WORKER_ID,
O.WorkPlace_ID as WORKPLACE_ID,
Case
When (select count(ID) from Relations where rel_def = 38 and leftside_id = SD.ID) = 0 then 0
else
1
end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
SD.ID as Source_ID,
JO.OverheadCostsInIncome as OverheadCostsInIncome,
0 as Acc_Date,
SD.DocDate$DATE as Doc_Date,
O.ObjVersion as ObjVersion,
SD.DocQueue_ID as SourceDocQueue_ID,
SD.Period_ID as SourcePeriod_ID,
SD.OrdNumber as SourceOrdNumber
from PLMoperations O
join PLMJobOrdersRoutines R On O.jobordersroutines_id = R.ID
join plmjobordersroutinead rad ON R.ID = rad.parent_id
join Storedocuments2 a ON a.AdditionalCosts_ID = rad.AdditionalCosts_ID
join Storedocuments SD ON SD.id = A.Parent_ID
join plmjoborders JO On JO.productiontask_id = a.productiontask_id
join PLMJOOUTPUTITEMS OI On OI.ID = R.parent_id
join PLMJONodes N On N.id = OI.owner_id
join Periods PS On PS.ID = SD.Period_ID
join DocQueues QS On QS.ID = SD.DocQueue_ID
WHERE (SD.DocumentType = '28') and (SD.DocDate$DATE >= :ADateFrom) and JO.Balanced = 'N' and JO.ID = :AJobOrderID
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_ID,
mInIncomeC,
Out_Acc_Date,
Out_Doc_Date,
Out_ObjVersion,
Out_SourceDocQueue_ID,
Out_SourcePeriod_ID,
Out_SourceOrdNumber
do
begin
Out_ValueType = 'VYR';
If ((mInIncomeC = 'A') Or (AAcceptJOSetting = 0)) then
begin
Suspend;
end
end
/* Pracovni listky - prace */
For
Select
a.productiontask_id as productiontask_id,
Case when (select sum(SalaryCosts) from PLMOperations where jobordersroutines_id=R.ID) < 0.01
then 0 Else
cast(
(cast(o.SalaryCosts as numeric(15,5))/
Cast((select sum(SalaryCosts) from PLMOperations where jobordersroutines_id=R.ID)as numeric(15,5))
) as numeric(18,8))
* Cast(rad.SalaryCosts as numeric(15,5)) End 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,
N.storecard_id as OUTPUTSTORECARD_ID,
O.PerformedBy_ID as WORKER_ID,
O.WorkPlace_ID as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 38 and leftside_id = SD.ID) = 0 then 0 else 1 end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
SD.ID as Source_ID,
JO.SalariesInIncome as SalariesInIncome,
0 as Acc_Date,
SD.DocDate$DATE as Doc_Date,
O.ObjVersion as ObjVersion,
SD.DocQueue_ID as SourceDocQueue_ID,
SD.Period_ID as SourcePeriod_ID,
SD.OrdNumber as SourceOrdNumber
from
PLMoperations O
join PLMJobOrdersRoutines R On O.jobordersroutines_id = R.ID
join plmjobordersroutinead rad ON R.ID = rad.parent_id
join Storedocuments2 a ON a.AdditionalCosts_ID = rad.AdditionalCosts_ID
join Storedocuments SD ON SD.id = A.Parent_ID
join plmjoborders JO On JO.productiontask_id = a.productiontask_id
join PLMJOOUTPUTITEMS OI On OI.ID = R.parent_id
join PLMJONodes N On N.id = OI.owner_id
join Periods PS On PS.ID = SD.Period_ID
join DocQueues QS On QS.ID = SD.DocQueue_ID
WHERE (SD.DocumentType = '28') and (SD.DocDate$DATE >= :ADateFrom) and JO.Balanced = 'N' and JO.ID = :AJobOrderID
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_ID,
mInIncomeC,
Out_Acc_Date,
Out_Doc_Date,
Out_ObjVersion,
Out_SourceDocQueue_ID,
Out_SourcePeriod_ID,
Out_SourceOrdNumber
do
begin
Out_ValueType = 'PRL';
If ((mInIncomeC = 'A') Or (AAcceptJOSetting = 0)) then
begin
Suspend;
end
end
/* KOOPERACE */
For
Select
a.productiontask_id as productiontask_id,
KAD.MaterialPrice + KAD.WorkPrice 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,
a.storecard_id as OUTPUTSTORECARD_ID,
'' as WORKER_ID,
'' as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 38 and leftside_id = SD.ID) = 0 then 0 else 1 end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
SD.ID as Source_ID,
JO.CooperationInIncome as CooperationInIncome,
0 as Acc_Date,
SD.DocDate$DATE as Doc_Date,
KAD.ObjVersion as ObjVersion,
SD.DocQueue_ID as SourceDocQueue_ID,
SD.Period_ID as SourcePeriod_ID,
SD.OrdNumber as SourceOrdNumber
FROM
PLMCooperationAD KAD
right join Storedocuments2 a ON a.AdditionalCosts_ID = kad.AdditionalCosts_ID
join Storedocuments SD ON SD.id = A.Parent_ID
join plmjoborders JO On JO.productiontask_id = a.productiontask_id
join Periods PS On PS.ID = SD.Period_ID
join DocQueues QS On QS.ID = SD.DocQueue_ID
WHERE (SD.DocumentType = '28') and (SD.DocDate$DATE >= :ADateFrom) and JO.Balanced = 'N' and JO.ID = :AJobOrderID
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_ID,
mInIncomeC,
Out_Acc_Date,
Out_Doc_Date,
Out_ObjVersion,
Out_SourceDocQueue_ID,
Out_SourcePeriod_ID,
Out_SourceOrdNumber
Do
Begin
Out_ValueType = 'KOO';
If ((mInIncomeC = '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,
S2.storecard_ID as OUTPUTSTORECARD_ID,
'' as WORKER_ID,
'' as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 38 and leftside_id = SD.ID) = 0 then 0 else 1 end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
SD.ID as Source_ID,
JO.MaterialExpenseInIncome as MaterialExpenseInIncome,
0 as Acc_Date,
SD.DocDate$DATE as Doc_Date,
B.ObjVersion as ObjVersion,
SD.DocQueue_ID as SourceDocQueue_ID,
SD.Period_ID as SourcePeriod_ID,
SD.OrdNumber as SourceOrdNumber
FROM
PLMFinishedProducts2 B
join storedocuments2 S2 on S2.id = B.storedoc2_id
join Storedocuments SD ON SD.id = s2.Parent_ID
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 = SD.Period_ID
join DocQueues QS On QS.ID = SD.DocQueue_ID
where (SD.DocumentType = '28') and (SD.DocDate$DATE >= :ADateFrom) and (JO.Balanced = 'N') and JO.ID = :AJobOrderID
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_ID,
mInIncomeC,
Out_Acc_Date,
Out_Doc_Date,
Out_ObjVersion,
Out_SourceDocQueue_ID,
Out_SourcePeriod_ID,
Out_SourceOrdNumber
Do
Begin
Out_ValueType = 'MAR';
If ((mInIncomeC = '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,
S2.storecard_ID as OUTPUTSTORECARD_ID,
'' as WORKER_ID,
'' as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 38 and leftside_id = SD.ID) = 0 then 0 else 1 end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
SD.ID as Source_ID,
JO.PriceInIncome as PriceInIncome,
0 as Acc_Date,
SD.DocDate$DATE as Doc_Date,
B.ObjVersion as ObjVersion,
SD.DocQueue_ID as SourceDocQueue_ID,
SD.Period_ID as SourcePeriod_ID,
SD.OrdNumber as SourceOrdNumber
FROM
PLMFinishedProducts2 B
join storedocuments2 S2 on S2.id = B.storedoc2_id
join Storedocuments SD ON SD.id = s2.Parent_ID
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 = SD.Period_ID
join DocQueues QS On QS.ID = SD.DocQueue_ID
where (SD.DocumentType = '28') and (SD.DocDate$DATE >= :ADateFrom) and JO.Balanced = 'N' and JO.ID = :AJobOrderID
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_ID,
mInIncomeI,
Out_Acc_Date,
Out_Doc_Date,
Out_ObjVersion,
Out_SourceDocQueue_ID,
Out_SourcePeriod_ID,
Out_SourceOrdNumber
Do
begin
Out_ValueType = 'CEN';
If ((mInIncomeI > 0) 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,
S2.storecard_ID as OUTPUTSTORECARD_ID,
'' as WORKER_ID,
'' as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 38 and leftside_id = SD.ID) = 0 then 0 else 1 end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
SD.ID as Source_ID,
JO.ConsumablesInIncome as ConsumablesInIncome,
0 as Acc_Date,
SD.DocDate$DATE as Doc_Date,
B.ObjVersion as ObjVersion,
SD.DocQueue_ID as SourceDocQueue_ID,
SD.Period_ID as SourcePeriod_ID,
SD.OrdNumber as SourceOrdNumber
FROM
PLMFinishedProducts2 B
join storedocuments2 S2 on S2.id = B.storedoc2_id
join Storedocuments SD ON SD.id = s2.Parent_ID
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 = SD.Period_ID
join DocQueues QS On QS.ID = SD.DocQueue_ID
where (SD.DocumentType = '28') and (SD.DocDate$DATE >= :ADateFrom) 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_ID,
mInIncomeI,
Out_Acc_Date,
Out_Doc_Date,
Out_ObjVersion,
Out_SourceDocQueue_ID,
Out_SourcePeriod_ID,
Out_SourceOrdNumber
Do
Begin
Out_ValueType = 'SPM';
If ((mInIncomeI > 0) Or (AAcceptJOSetting = 0)) then
Begin
Suspend;
End
End
End