BEGIN
RC2_LocalTAmount = 0;
RC2_LocalTAmountWithoutVAT = 0;
RC2_Quantity = 0;
SD2_LocalTAmount = 0;
SD2_ClosingOrder = 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 = '';
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 -- bacha, jeste vyhodit RowType - v posu neni, mozna i neco jineho (XXX)
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;
--dodaci listy
SQLStat = '';
SQLStat = SQLStat || 'select ';
SQLStat = SQLStat || ' a.parent_id, ';
SQLStat = SQLStat || ' sd2.StoreCard_ID, sd2.Store_ID, ';
SQLStat = SQLStat || ' sd2.Division_ID, sd2.BusTransaction_ID, sd2.BusOrder_ID, sd2.BusProject_ID, ';
SQLStat = SQLStat || ' sum(sd2.LocalTAmount), ';
SQLStat = SQLStat || ' min(sd2.ClosingOrder) ';
SQLStat = SQLStat || 'from ';
SQLStat = SQLStat || ' possummareddocuments5 a ';
SQLStat = SQLStat || ' join possummareddocuments psd on a.parent_id=psd.id ';
SQLStat = SQLStat || ' join storedocuments sd on a.document_id=sd.id and a.documenttype=''21'' ';
SQLStat = SQLStat || ' join storedocuments2 sd2 on sd2.parent_id=sd.id ';
SQLStat = SQLStat || ' inner join Firms F on F.ID = psd.Firm_ID ';
SQLStat = SQLStat || ' inner join DocQueues Q on Q.ID = psd.DocQueue_ID ';
SQLStat = SQLStat || ' inner join Periods P on P.ID = psd.Period_ID ';
SQLStat = SQLStat || ' inner join Divisions D on D.ID = sd2.Division_ID ';
--podminky do from
SQLFromRun = SQLFrom;
SQLFromRun = ib_string_replace(SQLFromRun, ':AliasHeader', 'PSD');
SQLFromRun = ib_string_replace(SQLFromRun, ':AliasRow', 'SD2');
SQLStat = SQLStat || ' ' || SQLFromRun;
--podminky
if (SQLWhere is not null) then begin
SQLWhereRun = SQLWhere;
SQLWhereRun = ib_string_replace(SQLWhereRun, ':AliasHeader', 'PSD');
SQLWhereRun = ib_string_replace(SQLWhereRun, ':AliasRow', 'SD2');
SQLStat = SQLStat || ' where ' || SQLWhereRun;
end
--zbytek dotazu
SQLStat = SQLStat || 'group by ';
SQLStat = SQLStat || ' a.parent_id, sd2.storecard_id, sd2.store_id, sd2.division_id, sd2.busorder_id, sd2.bustransaction_id, sd2.busProject_id ';
--execution
--exception ErrPeriods SQLStat;
for EXECUTE STATEMENT SQLStat
into
:PSD_ID,
:StoreCard_ID, :Store_ID,
:Division_ID, :BusTransaction_ID, :BusOrder_ID, :BusProject_ID,
:SD2_LocalTAmount,
:SD2_ClosingOrder
do
begin
select
sum(a.LocalTAmount),
sum(a.LocalTAmountWithoutVAT),
sum(a.Quantity * a.UnitRate),
max(s.Code), max(s.Name),
max(f.ID), max(f.Code), max(f.Name),
max(st.Code), max(st.Name),
max(D.Code), max(D.Name),
max(psd.DocDate$date),
max(Q.ID), max(Q.Code), max(psd.OrdNumber), max(p.ID), max(p.Code),
case when max(f.Firm_ID) is null then max(f.ID) else max(f.Firm_ID) end as FirmActual_ID
from
possummareddocuments2 a
join possummareddocuments psd on psd.id = a.parent_id
inner join Firms F on F.ID = psd.Firm_ID
inner join DocQueues Q on Q.ID = psd.DocQueue_ID
inner join Periods P on P.ID = psd.Period_ID
inner join Divisions D on D.ID = a.Division_ID
inner join StoreCards S on S.ID = a.StoreCard_ID
inner join Stores St on St.ID = a.Store_ID
where
a.parent_id = :PSD_ID
and a.StoreCard_ID = :StoreCard_ID
and a.Store_ID = :Store_ID
and a.Division_ID = :Division_ID
and (((a.BusTransaction_ID = :BusTransaction_ID) or
((a.BusTransaction_ID is null) and (:BusTransaction_ID is null))))
and (((a.BusOrder_ID = :BusOrder_ID) or
((a.BusOrder_ID is null) and (:BusOrder_ID is null))))
/* Odkomentovat při řešení ANA-3/2009 fáze 2
and ((a.BusProject_ID = :BusProject_ID) or
((a.BusProject_ID is null) and (:BusProject_ID is null)))
*/
into
:RC2_LocalTAmount, :RC2_LocalTAmountWithoutVAT, :RC2_Quantity,
:StoreCard_Code, :StoreCard_Name,
:Firm_ID, :Firm_Code, :Firm_Name,
:Store_Code, :Store_Name,
:Division_Code, :Division_Name,
:DocDate,
:DocQueue_ID, :DocQueue_Code, :OrdNumber, :Period_ID, :Period_Code,
:FirmActual_ID;
Suspend;
end
RC2_LocalTAmount = 0;
RC2_LocalTAmountWithoutVAT = 0;
RC2_Quantity = 0;
SD2_LocalTAmount = 0;
SD2_ClosingOrder = 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 = '';
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;
--vraceni
SQLStat = '';
SQLStat = SQLStat || 'select ';
SQLStat = SQLStat || ' a.parent_id, ';
SQLStat = SQLStat || ' sd2.StoreCard_ID, sd2.Store_ID, ';
SQLStat = SQLStat || ' sd2.Division_ID, sd2.BusTransaction_ID, sd2.BusOrder_ID, sd2.BusProject_ID, ';
SQLStat = SQLStat || ' sum(sd2.LocalTAmount), ';
SQLStat = SQLStat || ' min(sd2.ClosingOrder) ';
SQLStat = SQLStat || 'from ';
SQLStat = SQLStat || ' possummareddocuments5 a ';
SQLStat = SQLStat || ' join possummareddocuments psd on a.parent_id=psd.id ';
SQLStat = SQLStat || ' join storedocuments sd on a.document_id=sd.id and a.documenttype=''20'' ';
SQLStat = SQLStat || ' join storedocuments2 sd2 on sd2.parent_id=sd.id ';
SQLStat = SQLStat || ' inner join Firms F on F.ID = psd.Firm_ID ';
SQLStat = SQLStat || ' inner join DocQueues Q on Q.ID = psd.DocQueue_ID ';
SQLStat = SQLStat || ' inner join Periods P on P.ID = psd.Period_ID ';
SQLStat = SQLStat || ' inner join Divisions D on D.ID = sd2.Division_ID ';
--podminky do from
SQLFromRun = SQLFrom;
SQLFromRun = ib_string_replace(SQLFromRun, ':AliasHeader', 'PSD');
SQLFromRun = ib_string_replace(SQLFromRun, ':AliasRow', 'SD2');
SQLStat = SQLStat || ' ' || SQLFromRun;
--podminky
if (SQLWhere is not null) then begin
SQLWhereRun = SQLWhere;
SQLWhereRun = ib_string_replace(SQLWhereRun, ':AliasHeader', 'PSD');
SQLWhereRun = ib_string_replace(SQLWhereRun, ':AliasRow', 'SD2');
SQLStat = SQLStat || ' where ' || SQLWhereRun;
end
--zbytek dotazu
SQLStat = SQLStat || 'group by ';
SQLStat = SQLStat || ' a.parent_id, sd2.storecard_id, sd2.store_id, sd2.division_id, sd2.busorder_id, sd2.bustransaction_id, sd2.busProject_id ';
--execution
--exception ErrPeriods SQLStat;
for EXECUTE STATEMENT SQLStat
into
:PSD_ID,
:StoreCard_ID, :Store_ID,
:Division_ID, :BusTransaction_ID, :BusOrder_ID, :BusProject_ID,
:XSD2_LocalTAmount,
:SD2_ClosingOrder
do
begin
select
sum(-a.LocalTAmount),
sum(-a.LocalTAmountWithoutVAT),
sum(-a.Quantity * a.UnitRate),
max(s.Code), max(s.Name),
max(f.ID), max(f.Code), max(f.Name),
max(st.Code), max(st.Name),
max(D.Code), max(D.Name),
max(psd.DocDate$date),
max(Q.ID), max(Q.Code), max(psd.OrdNumber), max(p.ID), max(p.Code),
case when max(f.Firm_ID) is null then max(f.ID) else max(f.Firm_ID) end as FirmActual_ID
from
possummareddocuments3 a
join possummareddocuments psd on psd.id = a.parent_id
inner join Firms F on F.ID = psd.Firm_ID
inner join DocQueues Q on Q.ID = psd.DocQueue_ID
inner join Periods P on P.ID = psd.Period_ID
inner join Divisions D on D.ID = a.Division_ID
inner join StoreCards S on S.ID = a.StoreCard_ID
inner join Stores St on St.ID = a.Store_ID
where
a.parent_id = :PSD_ID
and ((a.INCOMEDOCTYPE='20') or (a.INCOMEDOCTYPE=''))
and a.StoreCard_ID = :StoreCard_ID
and a.Store_ID = :Store_ID
and a.Division_ID = :Division_ID
and ((a.BusTransaction_ID = :BusTransaction_ID) or
((a.BusTransaction_ID is null) and (:BusTransaction_ID is null)))
and ((a.BusOrder_ID = :BusOrder_ID) or
((a.BusOrder_ID is null) and (:BusOrder_ID is null)))
/* Odkomentovat při řešení ANA-3/2009 fáze 2
and ((a.BusProject_ID = :BusProject_ID) or
((a.BusProject_ID is null) and (:BusProject_ID is null)))
*/
into
:RCR2_LocalTAmount, :RCR2_LocalTAmountWithoutVAT, :RCR2_Quantity,
:StoreCard_Code, :StoreCard_Name,
:Firm_ID, :Firm_Code, :Firm_Name,
:Store_Code, :Store_Name,
:Division_Code, :Division_Name,
:DocDate,
:DocQueue_ID, :DocQueue_Code, :OrdNumber, :Period_ID, :Period_Code,
:FirmActual_ID;
Suspend;
end
RC2_LocalTAmount = 0;
RC2_LocalTAmountWithoutVAT = 0;
RC2_Quantity = 0;
SD2_LocalTAmount = 0;
SD2_ClosingOrder = 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 = '';
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;
--vratky
SQLStat = '';
SQLStat = SQLStat || 'select ';
SQLStat = SQLStat || ' a.parent_id, ';
SQLStat = SQLStat || ' sd2.StoreCard_ID, sd2.Store_ID, ';
SQLStat = SQLStat || ' sd2.Division_ID, sd2.BusTransaction_ID, sd2.BusOrder_ID, sd2.BusProject_ID, ';
SQLStat = SQLStat || ' sum(sd2.LocalTAmount), ';
SQLStat = SQLStat || ' min(sd2.ClosingOrder) ';
SQLStat = SQLStat || 'from ';
SQLStat = SQLStat || ' possummareddocuments5 a ';
SQLStat = SQLStat || ' join possummareddocuments psd on a.parent_id=psd.id ';
SQLStat = SQLStat || ' join storedocuments sd on a.document_id=sd.id and a.documenttype=''23'' ';
SQLStat = SQLStat || ' join storedocuments2 sd2 on sd2.parent_id=sd.id ';
SQLStat = SQLStat || ' inner join Firms F on F.ID = psd.Firm_ID ';
SQLStat = SQLStat || ' inner join DocQueues Q on Q.ID = psd.DocQueue_ID ';
SQLStat = SQLStat || ' inner join Periods P on P.ID = psd.Period_ID ';
SQLStat = SQLStat || ' inner join Divisions D on D.ID = sd2.Division_ID ';
--podminky do from
SQLFromRun = SQLFrom;
SQLFromRun = ib_string_replace(SQLFromRun, ':AliasHeader', 'PSD');
SQLFromRun = ib_string_replace(SQLFromRun, ':AliasRow', 'SD2');
SQLStat = SQLStat || ' ' || SQLFromRun;
--podminky
if (SQLWhere is not null) then begin
SQLWhereRun = SQLWhere;
SQLWhereRun = ib_string_replace(SQLWhereRun, ':AliasHeader', 'PSD');
SQLWhereRun = ib_string_replace(SQLWhereRun, ':AliasRow', 'SD2');
SQLStat = SQLStat || ' where ' || SQLWhereRun;
end
--zbytek dotazu
SQLStat = SQLStat || 'group by ';
SQLStat = SQLStat || ' a.parent_id, sd2.storecard_id, sd2.store_id, sd2.division_id, sd2.busorder_id, sd2.bustransaction_id, sd2.busProject_id ';
--execution
--exception ErrPeriods SQLStat;
for EXECUTE STATEMENT SQLStat
into
:PSD_ID,
:StoreCard_ID, :Store_ID,
:Division_ID, :BusTransaction_ID, :BusOrder_ID, :BusProject_ID,
:XSD2_LocalTAmount,
:SD2_ClosingOrder
do
begin
select
sum(-a.LocalTAmount),
sum(-a.LocalTAmountWithoutVAT),
sum(-a.Quantity * a.UnitRate),
max(s.Code), max(s.Name),
max(f.ID), max(f.Code), max(f.Name),
max(st.Code), max(st.Name),
max(D.Code), max(D.Name),
max(psd.DocDate$date),
max(Q.ID), max(Q.Code), max(psd.OrdNumber), max(p.ID), max(p.Code),
case when max(f.Firm_ID) is null then max(f.ID) else max(f.Firm_ID) end as FirmActual_ID
from
possummareddocuments3 a
join possummareddocuments psd on psd.id = a.parent_id
inner join Firms F on F.ID = psd.Firm_ID
inner join DocQueues Q on Q.ID = psd.DocQueue_ID
inner join Periods P on P.ID = psd.Period_ID
inner join Divisions D on D.ID = a.Division_ID
inner join StoreCards S on S.ID = a.StoreCard_ID
inner join Stores St on St.ID = a.Store_ID
where
a.parent_id = :PSD_ID
and ((a.INCOMEDOCTYPE='23') or (a.INCOMEDOCTYPE=''))
and a.StoreCard_ID = :StoreCard_ID
and a.Store_ID = :Store_ID
and a.Division_ID = :Division_ID
and ((a.BusTransaction_ID = :BusTransaction_ID) or
((a.BusTransaction_ID is null) and (:BusTransaction_ID is null)))
and ((a.BusOrder_ID = :BusOrder_ID) or
((a.BusOrder_ID is null) and (:BusOrder_ID is null)))
/* Odkomentovat při řešení ANA-3/2009 fáze 2
and ((a.BusProject_ID = :BusProject_ID) or
((a.BusProject_ID is null) and (:BusProject_ID is null)))
*/
into
:RCR2_LocalTAmount, :RCR2_LocalTAmountWithoutVAT, :RCR2_Quantity,
:StoreCard_Code, :StoreCard_Name,
:Firm_ID, :Firm_Code, :Firm_Name,
:Store_Code, :Store_Name,
:Division_Code, :Division_Name,
:DocDate,
:DocQueue_ID, :DocQueue_Code, :OrdNumber, :Period_ID, :Period_Code,
:FirmActual_ID;
Suspend;
end
end;