BEGIN
II2_LocalTAmount = 0;
II2_LocalTAmountWithoutVAT = 0;
II2_Quantity = 0;
RC2_LocalTAmount = 0;
RC2_LocalTAmountWithoutVAT = 0;
RC2_Quantity = 0;
SD2_LocalTAmount = 0;
SD2_ClosingOrder = 0;
ICN2_LocalTAmount = 0;
ICN2_LocalTAmountWithoutVAT = 0;
ICN2_Quantity = 0;
RCR2_LocalTAmount = 0;
RCR2_LocalTAmountWithoutVAT = 0;
RCR2_Quantity = 0;
XSD2_LocalTAmount = 0;
StoreCard_ID = null;
StoreCard_Code = '';
StoreCard_Name = '';
Firm_ID = null;
Firm_Code = '';
Firm_Name = '';
Parent_ID = null;
Store_ID = null;
Store_Code = '';
Store_Name = '';
Division_ID = null;
Division_Code = '';
Division_Name = '';
DocDate = 0;
DocQueue_ID = null;
DocQueue_Code = '';
OrdNumber = 0;
Period_ID = null;
Period_Code = '';
FirmActual_ID = null;
--konstrukce where casti
Select AToWhere, AToFrom from SOLDGOODSConds(:DocDateUsed ,:DocDateFrom, :DocDateTo, :StoreCardSelID,
:InDivisionSelID, :FirmSelID, :InBusOrderSelID, :InBusTransactionSelID, :InBusProjectSelID, :StoreSelID, :CountrySelID,
:CurrencySelID, :CreatedBySelID, :CorrectedBySelID, :DocQueueSelID, :InDivisionsWithChilds,
:InBusOrdersWithChilds, :InBusTransactionsWithChilds, :InBusProjectsWithChilds, :VATDateUsed, :VATDateFrom,
:VATDateTo, :FirmOfficeSelID, :AccDateUsed, :AccDateFrom, :AccDateTo, :ABusOrdersWithNull, :ABusTransactionsWithNull,
:ABusProjectsWithNull) into :SQLWhere, :SQLFrom;
--faktury vydané
SQLStat = '';
SQLStat = SQLStat || 'select ';
SQLStat = SQLStat || ' II2.Division_ID, II2.Store_ID, II2.Parent_ID, II2.ProvideRow_ID, ';
SQLStat = SQLStat || ' (II2.LocalTAmount + Coalesce(XXX.LocalTAmount, 0)) as II2_LocalTAmount, ';
SQLStat = SQLStat || ' (II2.LocalTAmountWithoutVAT + Coalesce(XXX.LocalTAmountWithoutVAT, 0)) as II2_LocalTAmountWithoutVAT, ';
SQLStat = SQLStat || ' II2.Quantity, II2.StoreCard_ID, ';
SQLStat = SQLStat || ' II.OrdNumber, II.DocDate$Date, ';
SQLStat = SQLStat || ' F.ID, F.Code, F.Name, ';
SQLStat = SQLStat || ' P.ID, P.Code, ';
SQLStat = SQLStat || ' Q.ID, Q.Code, ';
SQLStat = SQLStat || ' D.Code, D.Name, ';
SQLStat = SQLStat || ' case when F.Firm_ID is null then F.ID else F.Firm_ID end as FirmActual_ID ';
SQLStat = SQLStat || 'from IssuedInvoices2 II2 ';
SQLStat = SQLStat || ' inner join IssuedInvoices II on II2.Parent_ID = II.ID ';
SQLStat = SQLStat || ' inner join Firms F on F.ID = II.Firm_ID ';
SQLStat = SQLStat || ' inner join DocQueues Q on Q.ID = II.DocQueue_ID ';
SQLStat = SQLStat || ' inner join Periods P on P.ID = II.Period_ID ';
SQLStat = SQLStat || ' inner join Divisions D on D.ID = II2.Division_ID ';
SQLStat = SQLStat || ' left join ';
SQLStat = SQLStat || ' ( ';
SQLStat = SQLStat || ' select R.RightSide_ID, sum(II2.LocalTAmount) as LocalTAmount, sum(II2.LocalTAmountWithoutVAT) as LocalTAmountWithoutVAT ';
SQLStat = SQLStat || ' from Relations R ';
SQLStat = SQLStat || ' join IssuedInvoices2 II2 on (II2.ID=R.LeftSide_ID) ';
SQLStat = SQLStat || ' where R.Rel_def=2100 ';
SQLStat = SQLStat || ' group by RightSide_ID ';
SQLStat = SQLStat || ' ) XXX on XXX.RightSide_ID = II2.ID ';
--podminky do from
SQLFromRun = SQLFrom;
SQLFromRun = ib_string_replace(SQLFromRun, ':AliasHeader', 'II');
SQLFromRun = ib_string_replace(SQLFromRun, ':AliasRow', 'II2');
SQLStat = SQLStat || ' ' || SQLFromRun;
SQLStat = SQLStat || ' where ';
--podminky
SQLWhereRun = SQLWhere;
SQLWhereRun = ib_string_replace(SQLWhereRun, ':AliasHeader', 'II');
SQLWhereRun = ib_string_replace(SQLWhereRun, ':AliasRow', 'II2');
SQLStat = SQLStat || SQLWhereRun;
--execution
--exception ErrPeriods SQLStat;
for EXECUTE STATEMENT SQLStat
into
:Division_ID, :Store_ID, :Parent_ID, :II2_ProvideRow_ID,
:II2_LocalTAmount, :II2_LocalTAmountWithoutVAT,
:II2_Quantity, :StoreCard_ID,
:OrdNumber, :DocDate,
Firm_ID, Firm_Code, Firm_Name,
:Period_ID, :Period_Code,
:DocQueue_ID, :DocQueue_Code,
Division_Code, Division_Name,
:FirmActual_ID
do begin
--dodaci listy
SD2_ClosingOrder = 0;
StoreUnitPrice = 0;
select
case
when SD2.Quantity = 0 then 0
else
SD2.LocalTAmount/SD2.Quantity
end,
SD2.ClosingOrder
from StoreDocuments2 SD2 where SD2.ID = :II2_ProvideRow_ID
into :StoreUnitPrice, :SD2_ClosingOrder;
select SC.Code, SC.Name from StoreCards SC where SC.ID = :StoreCard_ID
into StoreCard_Code, StoreCard_Name;
select S.Code, S.Name from Stores S where S.ID = :Store_ID
into Store_Code, Store_Name;
SD2_LocalTAmount = StoreUnitPrice * II2_Quantity;
Suspend;
Parent_ID = null;
II2_ProvideRow_ID = null;
II2_LocalTAmount = 0;
II2_LocalTAmountWithoutVAT = 0;
II2_Quantity = 0;
StoreCard_ID = null;
SD2_LocalTAmount = 0;
StoreCard_Code = '';
StoreCard_Name = '';
Firm_ID = null;
Firm_Code = '';
Firm_Name = '';
Store_ID = null;
Store_Code = '';
Store_Name = '';
Division_ID = null;
Division_Code = '';
Division_Name = '';
DocDate = 0;
DocQueue_ID = null;
DocQueue_Code = '';
OrdNumber = 0;
Period_ID = null;
Period_Code = '';
FirmActual_ID = null;
end
--dobropisy
SQLStat = '';
SQLStat = SQLStat || 'select ';
SQLStat = SQLStat || ' ICN2.Division_ID, ICN2.Store_ID, ICN2.Parent_ID, ICN2.ProvideRow_ID, ';
SQLStat = SQLStat || ' (ICN2.LocalTAmount + Coalesce(XXX.LocalTAmount, 0)) as IC2_LocalTAmount, ';
SQLStat = SQLStat || ' (ICN2.LocalTAmountWithoutVAT + Coalesce(XXX.LocalTAmountWithoutVAT, 0)) as IC2_LocalTAmountWithoutVAT, ';
SQLStat = SQLStat || ' ICN2.Quantity, ICN2.StoreCard_ID, ';
SQLStat = SQLStat || ' ICN.OrdNumber, ICN.DocDate$Date, ';
SQLStat = SQLStat || ' F.ID, F.Code, F.Name, ';
SQLStat = SQLStat || ' P.ID, P.Code, ';
SQLStat = SQLStat || ' Q.ID, Q.Code, ';
SQLStat = SQLStat || ' D.Code, D.Name, ';
SQLStat = SQLStat || ' case when F.Firm_ID is null then F.ID else F.Firm_ID end as FirmActual_ID ';
SQLStat = SQLStat || 'from IssuedCreditNotes2 ICN2 ';
SQLStat = SQLStat || ' inner join IssuedCreditNotes ICN on ICN.ID = ICN2.Parent_ID ';
SQLStat = SQLStat || ' inner join Firms F on F.ID = ICN.Firm_ID ';
SQLStat = SQLStat || ' inner join DocQueues Q on Q.ID = ICN.DocQueue_ID ';
SQLStat = SQLStat || ' inner join Periods P on P.ID = ICN.Period_ID ';
SQLStat = SQLStat || ' inner join Divisions D on D.ID = ICN2.Division_ID ';
SQLStat = SQLStat || ' left join ';
SQLStat = SQLStat || ' ( ';
SQLStat = SQLStat || ' select R.RightSide_ID, sum(IC2.LocalTAmount) as LocalTAmount, sum(IC2.LocalTAmountWithoutVAT) as LocalTAmountWithoutVAT ';
SQLStat = SQLStat || ' from Relations R ';
SQLStat = SQLStat || ' join IssuedCreditNotes2 IC2 on (IC2.ID=R.LeftSide_ID) ';
SQLStat = SQLStat || ' where R.Rel_def=2101 ';
SQLStat = SQLStat || ' group by RightSide_ID ';
SQLStat = SQLStat || ' ) XXX on XXX.RightSide_ID = ICN2.ID ';
--podminky do from
SQLFromRun = SQLFrom;
SQLFromRun = ib_string_replace(SQLFromRun, ':AliasHeader', 'ICN');
SQLFromRun = ib_string_replace(SQLFromRun, ':AliasRow', 'ICN2');
SQLStat = SQLStat || ' ' || SQLFromRun;
SQLStat = SQLStat || ' where ';
--podminky
SQLWhereRun = SQLWhere; --bacha v tomto neni docdate ?? asi i v jinych
SQLWhereRun = ib_string_replace(SQLWhereRun, ':AliasHeader', 'ICN');
SQLWhereRun = ib_string_replace(SQLWhereRun, ':AliasRow', 'ICN2');
SQLStat = SQLStat || SQLWhereRun;
--execution
for EXECUTE STATEMENT SQLStat
into
:Division_ID, :Store_ID, :Parent_ID, :ICN2_ProvideRow_ID,
:ICN2_LocalTAmount, :ICN2_LocalTAmountWithoutVAT,
:ICN2_Quantity, :StoreCard_ID,
:OrdNumber, :DocDate,
Firm_ID, Firm_Code, Firm_Name,
:Period_ID, :Period_Code,
:DocQueue_ID, :DocQueue_Code,
Division_Code, Division_Name,
:FirmActual_ID
do begin
--vratky
SD2_ClosingOrder = 0;
StoreUnitPrice = 0;
select
case
when XSD2.Quantity = 0 then 0
else
XSD2.LocalTAmount/XSD2.Quantity
end,
XSD2.ClosingOrder
from StoreDocuments2 XSD2 where XSD2.ID = :ICN2_ProvideRow_ID
into :StoreUnitPrice, :SD2_ClosingOrder;
-- jedna se o financni dobropis, neexistuje k nemu vratka, interpretujeme to jako nulova cena v prvni uzaverce
if ((:SD2_ClosingOrder = 0) and (:ICN2_Quantity = 0)) then
SD2_ClosingOrder = 1;
select SC.Code, SC.Name from StoreCards SC where SC.ID = :StoreCard_ID
into StoreCard_Code, StoreCard_Name;
select S.Code, S.Name from Stores S where S.ID = :Store_ID
into Store_Code, Store_Name;
XSD2_LocalTAmount = StoreUnitPrice * ICN2_Quantity;
Suspend;
Parent_ID = null;
ICN2_ProvideRow_ID = null;
ICN2_LocalTAmount = 0;
ICN2_LocalTAmountWithoutVAT = 0;
ICN2_Quantity = 0;
StoreCard_ID = null;
XSD2_LocalTAmount = 0;
StoreCard_Code = '';
StoreCard_Name = '';
Firm_ID = null;
Firm_Code = '';
Firm_Name = '';
Store_ID = null;
Store_Code = '';
Store_Name = '';
Division_ID = null;
Division_Code = '';
Division_Name = '';
DocDate = 0;
DocQueue_ID = null;
DocQueue_Code = '';
OrdNumber = 0;
Period_ID = null;
Period_Code = '';
FirmActual_ID = null;
end
--pokladní príjmy
SQLStat = '';
SQLStat = SQLStat || 'select ';
SQLStat = SQLStat || ' RC2.Division_ID, RC2.Store_ID, RC2.Parent_ID, RC2.ProvideRow_ID, ';
SQLStat = SQLStat || ' RC2.LocalTAmount, RC2.LocalTAmountWithoutVAT, ';
SQLStat = SQLStat || ' RC2.Quantity, RC2.StoreCard_ID, ';
SQLStat = SQLStat || ' RC.OrdNumber, RC.DocDate$Date, ';
SQLStat = SQLStat || ' F.ID, F.Code, F.Name, ';
SQLStat = SQLStat || ' P.ID, P.Code, ';
SQLStat = SQLStat || ' Q.ID, Q.Code, ';
SQLStat = SQLStat || ' D.Code, D.Name, ';
SQLStat = SQLStat || ' case when F.Firm_ID is null then F.ID else F.Firm_ID end as FirmActual_ID ';
SQLStat = SQLStat || 'from CashReceived2 RC2 ';
SQLStat = SQLStat || ' inner join CashReceived RC on RC.ID = RC2.Parent_ID ';
SQLStat = SQLStat || ' inner join Firms F on F.ID = RC.Firm_ID ';
SQLStat = SQLStat || ' inner join DocQueues Q on Q.ID = RC.DocQueue_ID ';
SQLStat = SQLStat || ' inner join Periods P on P.ID = RC.Period_ID ';
SQLStat = SQLStat || ' inner join Divisions D on D.ID = RC2.Division_ID ';
--podminky do from
SQLFromRun = SQLFrom;
SQLFromRun = ib_string_replace(SQLFromRun, ':AliasHeader', 'RC');
SQLFromRun = ib_string_replace(SQLFromRun, ':AliasRow', 'RC2');
SQLStat = SQLStat || ' ' || SQLFromRun;
SQLStat = SQLStat || ' where ';
--podminky
SQLWhereRun = SQLWhere; --bacha v tomto neni docdate ?? asi i v jinych
SQLWhereRun = ib_string_replace(SQLWhereRun, ':AliasHeader', 'RC');
SQLWhereRun = ib_string_replace(SQLWhereRun, ':AliasRow', 'RC2');
SQLStat = SQLStat || SQLWhereRun;
--execution
for EXECUTE STATEMENT SQLStat
into
:Division_ID, :Store_ID, :Parent_ID, :RC2_ProvideRow_ID,
:RC2_LocalTAmount, :RC2_LocalTAmountWithoutVAT,
:RC2_Quantity, :StoreCard_ID,
:OrdNumber, :DocDate,
Firm_ID, Firm_Code, Firm_Name,
:Period_ID, :Period_Code,
:DocQueue_ID, :DocQueue_Code,
Division_Code, Division_Name,
:FirmActual_ID
do begin
--dodaci listy
SD2_ClosingOrder = 0;
StoreUnitPrice = 0;
select
case
when SD2.Quantity = 0 then 0
else
SD2.LocalTAmount/SD2.Quantity
end,
SD2.ClosingOrder
from StoreDocuments2 SD2 where SD2.ID = :RC2_ProvideRow_ID
into :StoreUnitPrice, :SD2_ClosingOrder;
select SC.Code, SC.Name from StoreCards SC where SC.ID = :StoreCard_ID
into StoreCard_Code, StoreCard_Name;
select S.Code, S.Name from Stores S where S.ID = :Store_ID
into Store_Code, Store_Name;
SD2_LocalTAmount = StoreUnitPrice * RC2_Quantity;
Suspend;
Parent_ID = null;
RC2_ProvideRow_ID = null;
RC2_LocalTAmount = 0;
RC2_LocalTAmountWithoutVAT = 0;
RC2_Quantity = 0;
StoreCard_ID = null;
SD2_LocalTAmount = 0;
StoreCard_Code = '';
StoreCard_Name = '';
Firm_ID = null;
Firm_Code = '';
Firm_Name = '';
Store_ID = null;
Store_Code = '';
Store_Name = '';
Division_ID = null;
Division_Code = '';
Division_Name = '';
DocDate = 0;
DocQueue_ID = null;
DocQueue_Code = '';
OrdNumber = 0;
Period_ID = null;
Period_Code = '';
FirmActual_ID = null;
end
--vrácení pokladních príjmu
SQLStat = '';
SQLStat = SQLStat || 'select ';
SQLStat = SQLStat || ' RCR2.Division_ID, RCR2.Store_ID, RCR2.Parent_ID, RCR2.ProvideRow_ID, ';
SQLStat = SQLStat || ' RCR2.LocalTAmount, RCR2.LocalTAmountWithoutVAT, ';
SQLStat = SQLStat || ' RCR2.Quantity, RCR2.StoreCard_ID, ';
SQLStat = SQLStat || ' RCR.OrdNumber, RCR.DocDate$Date, ';
SQLStat = SQLStat || ' F.ID, F.Code, F.Name, ';
SQLStat = SQLStat || ' P.ID, P.Code, ';
SQLStat = SQLStat || ' Q.ID, Q.Code, ';
SQLStat = SQLStat || ' D.Code, D.Name, ';
SQLStat = SQLStat || ' case when F.Firm_ID is null then F.ID else F.Firm_ID end as FirmActual_ID ';
SQLStat = SQLStat || 'from RefundedCashReceived2 RCR2 ';
SQLStat = SQLStat || ' inner join RefundedCashReceived RCR on RCR.ID = RCR2.Parent_ID ';
SQLStat = SQLStat || ' inner join Firms F on F.ID = RCR.Firm_ID ';
SQLStat = SQLStat || ' inner join DocQueues Q on Q.ID = RCR.DocQueue_ID ';
SQLStat = SQLStat || ' inner join Periods P on P.ID = RCR.Period_ID ';
SQLStat = SQLStat || ' inner join Divisions D on D.ID = RCR2.Division_ID ';
--podminky do from
SQLFromRun = SQLFrom;
SQLFromRun = ib_string_replace(SQLFromRun, ':AliasHeader', 'RCR');
SQLFromRun = ib_string_replace(SQLFromRun, ':AliasRow', 'RCR2');
SQLStat = SQLStat || ' ' || SQLFromRun;
SQLStat = SQLStat || ' where ';
--podminky
SQLWhereRun = SQLWhere; --bacha v tomto neni docdate ?? asi i v jinych
SQLWhereRun = ib_string_replace(SQLWhereRun, ':AliasHeader', 'RCR');
SQLWhereRun = ib_string_replace(SQLWhereRun, ':AliasRow', 'RCR2');
SQLStat = SQLStat || SQLWhereRun;
--execution
for EXECUTE STATEMENT SQLStat
into
:Division_ID, :Store_ID, :Parent_ID, :RCR2_ProvideRow_ID,
:RCR2_LocalTAmount, :RCR2_LocalTAmountWithoutVAT,
:RCR2_Quantity, :StoreCard_ID,
:OrdNumber, :DocDate,
Firm_ID, Firm_Code, Firm_Name,
:Period_ID, :Period_Code,
:DocQueue_ID, :DocQueue_Code,
Division_Code, Division_Name,
:FirmActual_ID
do begin
--vratky
SD2_ClosingOrder = 0;
StoreUnitPrice = 0;
select
case
when XSD2.Quantity = 0 then 0
else
XSD2.LocalTAmount/XSD2.Quantity
end,
XSD2.ClosingOrder
from StoreDocuments2 XSD2 where XSD2.ID = :RCR2_ProvideRow_ID
into :StoreUnitPrice, :SD2_ClosingOrder;
-- jedna se o financni dobropis, neexistuje k nemu vratka, interpretujeme to jako nulova cena v prvni uzaverce
if ((:SD2_ClosingOrder = 0) and (:RCR2_Quantity = 0)) then
SD2_ClosingOrder = 1;
select SC.Code, SC.Name from StoreCards SC where SC.ID = :StoreCard_ID
into StoreCard_Code, StoreCard_Name;
select S.Code, S.Name from Stores S where S.ID = :Store_ID
into Store_Code, Store_Name;
XSD2_LocalTAmount = StoreUnitPrice * RCR2_Quantity;
Suspend;
Parent_ID = null;
RCR2_ProvideRow_ID = null;
RCR2_LocalTAmount = 0;
RCR2_LocalTAmountWithoutVAT = 0;
RCR2_Quantity = 0;
StoreCard_ID = null;
XSD2_LocalTAmount = 0;
StoreCard_Code = '';
StoreCard_Name = '';
Firm_ID = null;
Firm_Code = '';
Firm_Name = '';
Store_ID = null;
Store_Code = '';
Store_Name = '';
Division_ID = null;
Division_Code = '';
Division_Name = '';
DocDate = 0;
DocQueue_ID = null;
DocQueue_Code = '';
OrdNumber = 0;
Period_ID = null;
Period_Code = '';
FirmActual_ID = null;
end
II2_LocalTAmount = 0;
II2_LocalTAmountWithoutVAT = 0;
II2_Quantity = 0;
ICN2_LocalTAmount = 0;
ICN2_LocalTAmountWithoutVAT = 0;
ICN2_Quantity = 0;
for select
RC2_LocalTAmount,
RC2_LocalTAmountWithoutVAT,
RC2_Quantity,
SD2_LocalTAmount,
SD2_ClosingOrder,
RCR2_LocalTAmount,
RCR2_LocalTAmountWithoutVAT,
RCR2_Quantity,
XSD2_LocalTAmount,
StoreCard_ID,
StoreCard_Code,
StoreCard_Name,
Firm_ID,
Firm_Code,
Firm_Name,
Store_ID,
Store_Code,
Store_Name,
Division_ID,
Division_Code,
Division_Name,
DocDate,
DocQueue_ID,
DocQueue_Code,
OrdNumber,
Period_ID,
Period_Code,
FirmActual_ID
from SoldGoodsPos(
:DocDateUsed,
:DocDateFrom,
:DocDateTo,
:StoreCardSelID,
:InDivisionSelID,
:FirmSelID,
:InBusOrderSelID,
:InBusTransactionSelID,
:InBusProjectSelID,
:StoreSelID,
:CountrySelID,
:CurrencySelID,
:CreatedBySelID,
:CorrectedBySelID,
:DocQueueSelID,
:InDivisionsWithChilds,
:InBusOrdersWithChilds,
:InBusTransactionsWithChilds,
:InBusProjectsWithChilds,
:VATDateUsed,
:VATDateFrom,
:VATDateTo,
:FirmOfficeSelID,
:AccDateUsed,
:AccDateFrom,
:AccDateTo,
:ABusOrdersWithNull,
:ABusTransactionsWithNull,
:ABusProjectsWithNull)
into
:RC2_LocalTAmount,
:RC2_LocalTAmountWithoutVAT,
:RC2_Quantity,
:SD2_LocalTAmount,
:SD2_ClosingOrder,
:RCR2_LocalTAmount,
:RCR2_LocalTAmountWithoutVAT,
:RCR2_Quantity,
:XSD2_LocalTAmount,
:StoreCard_ID,
:StoreCard_Code,
:StoreCard_Name,
:Firm_ID,
:Firm_Code,
:Firm_Name,
:Store_ID,
:Store_Code,
:Store_Name,
:Division_ID,
:Division_Code,
:Division_Name,
:DocDate,
:DocQueue_ID,
:DocQueue_Code,
:OrdNumber,
:Period_ID,
:Period_Code,
:FirmActual_ID
do suspend;
end;