BEGIN
-- *****************************************************************************
SELECT country_id FROM globdata INTO :mlocalcountry_id;
IF (mlocalcountry_id = '') THEN mlocalcountry_id = :avatcountry_id;
mIsLocal = 'A';
IF (mlocalcountry_id <> avatcountry_id) THEN
BEGIN
mIsLocal = 'N';
END
mvatsumdef_id = avatdefinitionid;
-- *****************************************************************************
mLimitAmount4Simplified = 10000.00;
mSumDocumentAmount = 0;
mSumDocumentID = '0000000000';
mSumDocumentType = '';
mSumDocumentSec = '';
FOR
SELECT
dq.documenttype documenttype,
a.document_id document_id,
CAST(MAX(a.simplifiedvatdocument) AS CHAR(1)) simplifiedvatdocument,
CAST(MAX(a.firm_id) AS CHAR(10)) firm_id,
CAST(MAX(f.vatidentnumber) AS VARCHAR(20)) vatidentnumber,
MAX(a.vatdate$date) vatdate$date,
CAST(MAX(CAST(dq.code || '-' || CAST(a.ordnumber AS VARCHAR(10)) || '/' || pe.code AS VARCHAR(30))) AS VARCHAR(30)) documentnumber,
CAST(MAX(CAST(dq.code || '-' || ib_lpad(CAST(a.ordnumber AS VARCHAR(8)), 8, '0') || '/' || pe.code AS VARCHAR(28))) AS VARCHAR(28)) sortdocnumber,
CAST(MAX(a.externalnumber) AS VARCHAR(30)) externalnumber,
CAST(MAX(a.varsymbol) AS VARCHAR(30)) varsymbol,
a.vatindex_id vatindex_id,
vi.vatrate_id vatrate_id,
MAX(vr.tariff) tariff,
--a.vatmode vatmode,
CASE :mlocalcountry_id
WHEN :avatcountry_id
THEN SUM(a.localtamountwithoutvat)
ELSE SUM(a.tamountwithoutvat)
END baseamount,
CASE :mlocalcountry_id
WHEN :avatcountry_id
THEN SUM(a.localtamount-a.localtamountwithoutvat)
ELSE SUM(a.tamount - a.tamountwithoutvat)
END vatamount,
a.drcarticle_id drcarticle_id,
CAST(MAX(a.sourcedoc_id) AS CHAR(10)) sourcedoc_id,
CAST(MAX(a.sourcedoctype) AS CHAR(2)) sourcedoctype,
CAST(MAX(a.relateddocname) AS VARCHAR(30)) relateddocname,
CAST(MAX(a.vatreportreference) AS VARCHAR(30)) vatreportreference,
CAST(MAX(a.vatreportpreference) AS VARCHAR(2)) vatreportpreference,
CAST(MAX(a.possumdoc_id) AS CHAR(10)) possumdoc_id,
MAX(a.VATAdmitDate$DATE) VATAdmitDate$DATE,
a.UsedRatio UsedRatio,
CAST(MAX(f.taxidentnumber) AS VARCHAR(20)) taxidentnumber
FROM
VATSUMMARY(:afirmselid, :adivisionsselid, :abusordersselid, :abustransactionsselid, :abusprojectsselid,
:avatdatefrom, :avatdateto, :adivisionswithchilds, :abusorderswithchilds, :abustransactionswithchilds, :abusprojectswithchilds,
:avatcountry_id, 'A', 'A') A
JOIN firms f ON f.id = a.firm_id
JOIN periods pe ON pe.id = a.period_id
JOIN docqueues dq ON dq.id = a.docqueue_id
LEFT JOIN drcarticles da ON da.id = a.drcarticle_id
JOIN vatindexes vi ON vi.id = a.vatindex_id
JOIN vatrates vr ON vr.id = vi.vatrate_id
GROUP BY
dq.documenttype, a.document_id, a.vatindex_id, vi.vatrate_id, a.vatmode, a.drcarticle_id, a.UsedRatio
ORDER BY
dq.documenttype, a.document_id, a.vatindex_id, vi.vatrate_id, a.vatmode, a.drcarticle_id, a.UsedRatio
INTO
:documenttype, :document_id, :simplifiedvatdocument, :firm_id, :vatidentnumber, :vatdate$date,
:documentnumber, :sortdocnumber, :externalnumber, :varsymbol,
:vatindex_id, :vatrate_id, :vatrate, /*:mvatmode,*/ :baseamount, :vatamount, :drcarticle_id,
:sourcedoc_id, :sourcedoctype, :sourcedocnumber, :mvatreportreference, :mvatreportpreference,
:possumdoc_id, :mVATAdmitDate$DATE, :mUsedRatio, :mTaxIdentNumber
DO BEGIN
mstrresult = '';
sectionid = '';
definitionrow_id = NULL;
baselinenumber = '';
taxlinenumber = '';
vatreportmode = '';
svatadmitdate$date = 0;
sdocumenttradetype = 0;
IF (mVATAdmitDate$DATE <> 0) THEN
BEGIN
VATDate$DATE = mVATAdmitDate$DATE;
END
IF (VATIdentNumber = '') THEN
BEGIN
VATIdentNumber = mTaxIdentNumber;
END
-- *****************************************************************************
-- Prideleni sekce podle radku definice DPH priznani
-- *****************************************************************************
IF (vatindex_id <> '') THEN
BEGIN
FOR
SELECT
a.id,
a.baselinenumber,
a.taxlinenumber,
(SELECT MAX(VATReportMode) FROM VATSummaryDefinitions2 WHERE Parent_ID = :mVATSumDef_ID AND VATIndex_ID = :VATIndex_ID)
FROM
vatsummarydefinitions2 a
WHERE
a.parent_id = :mvatsumdef_id AND a.vatindex_id = :vatindex_id
INTO :mdefinitionrow_id, :mbaselinenumber, :mtaxlinenumber, :mvatreportmode
DO
BEGIN
IF ((mbaselinenumber <> '') OR (mtaxlinenumber <> '')) THEN
BEGIN
IF (
(mbaselinenumber = '001A') OR
(mbaselinenumber = '002A') OR
(mtaxlinenumber = '033A') OR
(mbaselinenumber = '1A') OR
(mbaselinenumber = '2A') OR
(mtaxlinenumber = '33A')
) THEN
BEGIN
-- ************************************************************************
-- opravy zakladu a dane nedobytnych pohledavek v insolvenci jsou mimo limit
-- ************************************************************************
mIntResult = IB_FIND_TOKEN(Trim(UPPER(mvatreportmode)), 'ONP46');
IF (mIntResult = 0) THEN
BEGIN
mIntResult = IB_FIND_TOKEN(Trim(UPPER(mvatreportmode)), 'INS44');
END
IF ((mtaxlinenumber = '033A') OR
(mtaxlinenumber = '33A') OR
(mIntResult > 0)
) THEN
BEGIN
mStrResult = 'A4';
mSumDocumentID = document_id;
mSumDocumentType = documenttype;
mSumDocumentSec = mstrresult;
END
ELSE
IF (trim(vatidentnumber) = '') /*(simplifiedvatdocument = 'A')*/ THEN
BEGIN
mstrresult = 'A5';
mSumDocumentID = '0000000000';
mSumDocumentType = '';
mSumDocumentSec = '';
END
ELSE
BEGIN
IF ((mSumDocumentID = Document_ID) AND (mSumDocumentType = DocumentType)) THEN
BEGIN
mstrresult = mSumDocumentSec;
END
ELSE
BEGIN
IF (mIsLocal = 'A') THEN
SELECT COALESCE(SUM(LocalAmount), 0) FROM SumRowAmountsForDocument(:DocumentType, :Document_ID, 'N') INTO :mSumDocumentAmount;
ELSE
SELECT COALESCE(SUM(Amount), 0) FROM SumRowAmountsForDocument(:DocumentType, :Document_ID, 'N') INTO :mSumDocumentAmount;
IF (ABS(mSumDocumentAmount) > mLimitAmount4Simplified) THEN
BEGIN
mstrresult = 'A4';
END
ELSE
BEGIN
mstrresult = 'A5';
END
mSumDocumentID = document_id;
mSumDocumentType = documenttype;
mSumDocumentSec = mstrresult;
END
END
END
ELSE
IF (
(mbaselinenumber = '003A') OR
(mbaselinenumber = '004A') OR
(mbaselinenumber = '005A') OR
(mbaselinenumber = '006A') OR
(mbaselinenumber = '009A') OR
(mbaselinenumber = '012A') OR
(mbaselinenumber = '013A') OR
(mbaselinenumber = '3A') OR
(mbaselinenumber = '4A') OR
(mbaselinenumber = '5A') OR
(mbaselinenumber = '6A') OR
(mbaselinenumber = '9A') OR
(mbaselinenumber = '12A') OR
(mbaselinenumber = '13A')
) THEN
BEGIN
mstrresult = 'A2';
END
ELSE
IF (
(mbaselinenumber = '010A') OR
(mbaselinenumber = '011A') OR
(mbaselinenumber = '10A') OR
(mbaselinenumber = '11A')
) THEN
BEGIN
mstrresult = 'B1';
END
ELSE
IF (
(mbaselinenumber = '040A') OR
(mbaselinenumber = '041A') OR
(mtaxlinenumber = '034A') OR
(mbaselinenumber = '40A') OR
(mbaselinenumber = '41A') OR
(mtaxlinenumber = '34A')
) THEN
BEGIN
-- ************************************************************************
-- opravy zakladu a dane nedobytnych pohledavek v insolvenci jsou mimo limit
-- ************************************************************************
mIntResult = IB_FIND_TOKEN(Trim(UPPER(mvatreportmode)), 'ONP46');
IF (mIntResult = 0) THEN
BEGIN
mIntResult = IB_FIND_TOKEN(Trim(UPPER(mvatreportmode)), 'INS44');
END
IF ((mtaxlinenumber = '034A') OR
(mtaxlinenumber = '34A') OR
(mIntResult > 0)
) THEN
BEGIN
mStrResult = 'B2';
mSumDocumentID = document_id;
mSumDocumentType = documenttype;
mSumDocumentSec = mstrresult;
END
ELSE
-- ************************************************************************
-- je potreba zjistit castku zdanitelneho plneni vsech radku s VATRate > 0
-- ************************************************************************
IF (trim(vatidentnumber) = '') /*(simplifiedvatdocument = 'A')*/ THEN
BEGIN
mstrresult = 'B3';
mSumDocumentID = '0000000000';
mSumDocumentType = '';
mSumDocumentSec = '';
END
ELSE
BEGIN
IF ((mSumDocumentID = Document_ID) AND (mSumDocumentType = DocumentType)) THEN
BEGIN
mstrresult = mSumDocumentSec;
END
ELSE
BEGIN
IF (mIsLocal = 'A') THEN
SELECT COALESCE(SUM(LocalAmount), 0) FROM SumRowAmountsForDocument(:DocumentType, :Document_ID, 'N') INTO :mSumDocumentAmount;
ELSE
SELECT COALESCE(SUM(Amount), 0) FROM SumRowAmountsForDocument(:DocumentType, :Document_ID, 'N') INTO :mSumDocumentAmount;
IF (ABS(mSumDocumentAmount) > mLimitAmount4Simplified) THEN
BEGIN
mstrresult = 'B2';
END
ELSE
BEGIN
mstrresult = 'B3';
END
mSumDocumentID = document_id;
mSumDocumentType = documenttype;
mSumDocumentSec = mstrresult;
END
END
END
ELSE
IF (
(mbaselinenumber = '025A') OR
(mbaselinenumber = '25A')
) THEN
BEGIN
mstrresult = 'A1';
END
ELSE
IF (
(mbaselinenumber = '026A') OR
(mbaselinenumber = '26A')
) THEN
BEGIN
-- V A3 se vykazuje jen investicni zlato - token:IGOLD
mIntResult = IB_FIND_TOKEN(Trim(UPPER(mvatreportmode)), 'IGOLD');
IF (mIntResult > 0) THEN
BEGIN
mstrresult = 'A3';
END
END
IF (mstrresult <> '') THEN
BEGIN
sectionid = mstrresult;
definitionrow_id = mdefinitionrow_id;
baselinenumber = mbaselinenumber;
taxlinenumber = mtaxlinenumber;
vatreportmode = mvatreportmode;
LEAVE;
END
END
END
END
-- *****************************************************************************
IF (sectionid <> '') THEN
BEGIN
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = mVATReportPreference;
END
IF (mUsedRatio = 'A') THEN
BEGIN
IF (TRIM(VATReportMode) <> '') THEN
BEGIN
VATReportMode = TRIM(TRIM(VATReportMode) || ';RATIO');
END
ELSE
BEGIN
VATReportMode = 'RATIO';
END
END
--******************************************************************************
-- Prevzeti ExternalNumber ze zdrojoveho dokladu
--******************************************************************************
IF (documenttype = 'RC') THEN
BEGIN
externalnumber = '';
mvatreportreference = '';
IF ((SourceDoc_ID is NULL) OR (SourceDocType = '')) THEN
BEGIN
sourcedoc_id = NULL;
sourcedoctype = '';
SELECT sdocument_id, sdocumenttype
FROM reversechargedeclarations
WHERE id = :document_id
INTO :sourcedoc_id, :sourcedoctype;
END
IF (:sourcedoctype = '04') THEN
BEGIN
SELECT externalnumber, VATReportReference, VATAdmitDate$DATE, tradetype
FROM receivedinvoices
WHERE id = :sourcedoc_id INTO :sourcedocnumber, :mVATReportReference, :svatadmitdate$date, :sdocumenttradetype;
END
ELSE
IF (:sourcedoctype = '61') THEN
BEGIN
SELECT externalnumber, VATAdmitDate$DATE, tradetype
FROM receivedcreditnotes
WHERE id = :sourcedoc_id INTO :sourcedocnumber, :svatadmitdate$date, :sdocumenttradetype;
END
ELSE
IF (:sourcedoctype = '06') THEN
BEGIN
SELECT externalnumber, VATReportReference, VATAdmitDate$DATE, tradetype
FROM cashpaid
WHERE id = :sourcedoc_id INTO :sourcedocnumber, :mVATReportReference, :svatadmitdate$date, :sdocumenttradetype;
END
ELSE
IF (:sourcedoctype = '08') THEN
BEGIN
SELECT externalnumber, VATAdmitDate$DATE, tradetype
FROM refundedcashpaid
WHERE id = :sourcedoc_id INTO :sourcedocnumber, :svatadmitdate$date, :sdocumenttradetype;
END
ELSE
IF (:sourcedoctype = '64') THEN
BEGIN
SELECT externalnumber, VATReportReference, VATAdmitDate$DATE, tradetype
FROM vatreceiveddinvoices
WHERE id = :sourcedoc_id INTO :sourcedocnumber, :mVATReportReference, :svatadmitdate$date, :sdocumenttradetype;
END
ELSE
IF (:sourcedoctype = '65') THEN
BEGIN
SELECT externalnumber, VATAdmitDate$DATE, tradetype
FROM vatreceiveddcreditnotes
WHERE id = :sourcedoc_id INTO :sourcedocnumber, :svatadmitdate$date, :sdocumenttradetype;
END
ELSE
IF (:sourcedoctype = '12') THEN
BEGIN
SELECT externalnumber, VATReportReference, VATAdmitDate$DATE
FROM customsdeclarations
WHERE id = :sourcedoc_id INTO :sourcedocnumber, :mVATReportReference, :svatadmitdate$date;
END
sourcedocnumber = COALESCE (:sourcedocnumber, '');
END
--******************************************************************************
IF ((SourceDocNumber = '') AND (mVATReportReference <> '')) THEN
BEGIN
sourcedocnumber = COALESCE (mVATReportReference, '');
END
SUSPEND;
END
END
END