BEGIN
select Count(DT.ID) from DueTermLimits DT where
(:DuetermLimitSelID = '' or DT.ID in (select OBJ_ID from SELDAT where SEL_ID = :DuetermLimitSelID))
into :CountLimits;
--Zkonstruuje se where cast dotazu
SQLWhere = '';
SQLVATDate1 = '';
SQLVATDate2 = '';
SQLAccDate = '';
--Firm_ID
SQLWhere = SQLWhere || '( ';
SQLWhere = SQLWhere || 'RI.Firm_ID = ''' || :FirmOID || ''' or ';
Select AResult from SYS$CONCATSELECT('ID', 'Firms where Firm_ID = ''' || :FirmOID || '''') into :SQLConc;
SQLWhere = SQLWhere || 'RI.Firm_ID in (' || :SQLConc || ')';
SQLWhere = SQLWhere || ')';
--DocDate$Date
SQLWhere = SQLWhere || ' and RI.DocDate$Date >= ' || CAST(:DocDateFrom AS VARCHAR(100)) || ' and RI.DocDate$Date < ' || CAST(:DocDateTo AS VARCHAR(100));
--DocQueue_ID
if (:DocQueueSelID <> '') then begin
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :DocQueueSelID || '''') into :SQLConc;
SQLWhere = SQLWhere || ' and RI.DocQueue_ID in (' || :SQLConc || ')';
end
--OrdNumber
if (:OrdNumberUsed = 'A') then
SQLWhere = SQLWhere || ' and RI.OrdNumber between ' || :OrdNumberFrom || ' and ' || :OrdNumberTo;
--VATDate$Date
if (:VATDateUsed = 'A') then begin
SQLVATDate1 = ' and RI.VATDate$Date >= ' || CAST(:VATDateFrom AS VARCHAR(100)) || ' and RI.VATDate$Date < ' || CAST(:VATDateTo AS VARCHAR(100));
SQLVATDate2 = ' and RI.DOCDate$Date >= ' || CAST(:VATDateFrom AS VARCHAR(100)) || ' and RI.DOCDate$Date < ' || CAST(:VATDateTo AS VARCHAR(100));
end
--DueDate$Date
if (:DueDateUsed = 'A') then
SQLWhere = SQLWhere || ' and RI.DueDate$Date >= ' || CAST(:DueDateFrom AS VARCHAR(100)) || ' and RI.DueDate$Date < ' || CAST(:DueDateTo AS VARCHAR(100));
--AccDate$Date
if (:AccDateUsed = 'A') then
SQLAccDate = ' and RI.AccDate$Date >= ' || CAST(:AccDateFrom AS VARCHAR(100)) || ' and RI.AccDate$Date < ' || CAST(:AccDateTo AS VARCHAR(100));
--CreatedBy_ID
if (:CreatedBySelID <> '') then begin
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :CreatedBySelID || '''') into :SQLConc;
SQLWhere = SQLWhere || ' and RI.CreatedBy_ID in (' || :SQLConc || ')';
end
--CorrectedBy_ID
if (:CorrectedBySelID <> '') then begin
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :CorrectedBySelID || '''') into :SQLConc;
SQLWhere = SQLWhere || ' and RI.CorrectedBy_ID in (' || :SQLConc || ')';
end
/* Faktury prijate */
SQLStat = 'select RI.ID, RI.DueDate$Date, RI.VATDate$Date, RI.LocaLAmount, RI.Firm_ID from ReceivedInvoices RI where ' || SQLWhere || SQLVATDate1 || SQLAccDate;
for EXECUTE STATEMENT SQLStat
into :RI_ID, :RI_DueDate, :RI_VATDate, :RI_LocaLAmount, :Firm_ID
do begin
LAmount = 0;
if (WithoutDue = 'N') then begin
select count(LocaLAmount) from CalcAmountsByLimits('04', :RI_ID, :MainDate, :RI_LocaLAmount, :DuetermLimitSelID, :RI_DueDate)
where (LocaLAmount <> 0)
into :LAmount;
end
if ((WithoutDue = 'A') or (LAmount <> 0)) then
for select DT_ID, LocaLAmount, DT_DueTermLimit from CalcAmountsByLimits('04', :RI_ID, :MainDate, :RI_LocaLAmount, :DuetermLimitSelID, :RI_DueDate)
into :DT_ID, :LocaLAmount, :DT_DueTermLimit
do begin
Suspend;
end
end
/* zalohove listy prijate */
if (ReceivedDInvoicesUsed = 'A') then begin
SQLStat = 'select RI.ID, RI.DueDate$Date, RI.DOCDate$Date as VATDate$Date, RI.LocaLAmount, RI.Firm_ID from ReceivedDInvoices RI where ' || SQLWhere || SQLVATDate2;
for EXECUTE STATEMENT SQLStat
into :RI_ID, :RI_DueDate, :RI_VATDate, :RI_LocaLAmount, :Firm_ID
do begin
LAmount = 0;
if (WithoutDue = 'N') then begin
select count(LocaLAmount) from CalcAmountsByLimits('11', :RI_ID, :MainDate, :RI_LocaLAmount, :DuetermLimitSelID, :RI_DueDate)
where (LocaLAmount <> 0)
into :LAmount;
end
if ((WithoutDue = 'A') or (LAmount <> 0)) then
for select DT_ID, LocaLAmount, DT_DueTermLimit from CalcAmountsByLimits('11', :RI_ID, :MainDate, :RI_LocaLAmount, :DuetermLimitSelID, :RI_DueDate)
into :DT_ID, :LocaLAmount, :DT_DueTermLimit
do begin
Suspend;
end
end
end
/* JCD */
SQLStat = 'select RI.ID, RI.DueDate$Date, RI.VATDate$Date, RI.LocaLAmount, RI.Firm_ID from CustomsDeclarations RI where ' || SQLWhere || SQLVATDate1 || SQLAccDate;
for EXECUTE STATEMENT SQLStat
into :RI_ID, :RI_DueDate, :RI_VATDate, :RI_LocaLAmount, :Firm_ID
do begin
LAmount = 0;
if (WithoutDue = 'N') then begin
select count(LocaLAmount) from CalcAmountsByLimits('12', :RI_ID, :MainDate, :RI_LocaLAmount, :DuetermLimitSelID, :RI_DueDate)
where (LocaLAmount <> 0)
into :LAmount;
end
if ((WithoutDue = 'A') or (LAmount <> 0)) then
for select DT_ID, LocaLAmount, DT_DueTermLimit from CalcAmountsByLimits('12', :RI_ID, :MainDate, :RI_LocaLAmount, :DuetermLimitSelID, :RI_DueDate)
into :DT_ID, :LocaLAmount, :DT_DueTermLimit
do begin
Suspend;
end
end
/* dobropisy danovych zalohovych listu vydanych */
if (VatCreditnotesUsed = 'A') then begin
SQLStat = 'select RI.ID, RI.DueDate$Date, RI.VATDate$Date, RI.LocaLAmount, RI.Firm_ID from VATIssuedDCreditNotes RI where ' || SQLWhere || SQLVATDate1 || SQLAccDate;
for EXECUTE STATEMENT SQLStat
into :RI_ID, :RI_DueDate, :RI_VATDate, :RI_LocaLAmount, :Firm_ID
do begin
LAmount = 0;
if (WithoutDue = 'N') then begin
select count(LocaLAmount) from CalcAmountsByLimits('63', :RI_ID, :MainDate, :RI_LocaLAmount, :DuetermLimitSelID, :RI_DueDate)
where (LocaLAmount <> 0)
into :LAmount;
end
if ((WithoutDue = 'A') or (LAmount <> 0)) then
for select DT_ID, LocaLAmount, DT_DueTermLimit from CalcAmountsByLimits('63', :RI_ID, :MainDate, :RI_LocaLAmount, :DuetermLimitSelID, :RI_DueDate)
into :DT_ID, :LocaLAmount, :DT_DueTermLimit
do begin
Suspend;
end
end
end
end;