BEGIN
-- *****************************************************************************
-- Vstupni zpetne doklady: VATMode=N/A, simplifiedVATDocument<>N/A, isfiscalized<>'A' **
-- *****************************************************************************
mSectionID = 'C2';
SELECT country_id FROM globdata INTO :mLocalCountry_ID;
IF (mLocalCountry_ID = '') THEN
mLocalCountry_ID = :AVATCountry_ID;
-- *****************************************************************************
/* radky DP */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
b.vatadmitdate$date vatdate$date,
b.externalnumber DocNumber,
b.source_id Source_ID,
dqsrc.documenttype SourceDocType,
src.externalnumber SourceDocNumber,
a.VATRate VATRate,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN (-1*a.LocalTAmountWithoutVAT)
ELSE (-1*a.TAmountWithoutVAT)
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN (-1*(a.LocalTAmount-a.LocalTAmountWithoutVAT))
ELSE (-1*(a.TAmount - a.TAmountWithoutVAT))
END VATAmount,
0 AllowanceAmount,
NULL DRC_ID, '' DRCCode, 0 DRCQuantity, '' DRCQUnit,
a.VATIndex_ID VATIndex_ID,
vi.income Income,
B.VATReportPreference VATReportPreference,
dq.Code || '-' || IB_LPad(CAST(b.OrdNumber AS VARCHAR(8)), 8, '0') || '/' || pe.Code SortDocNumber,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID,
B.VarSymbol VarSymbol
FROM
receivedcreditnotes2 a
JOIN receivedcreditnotes b ON b.id = a.parent_id
JOIN receivedinvoices src ON src.id = b.source_id
JOIN firms f ON f.id = b.firm_id
JOIN periods pe ON pe.id = b.period_id
JOIN periods pesrc ON pesrc.id = src.period_id
JOIN docqueues dq ON dq.id = b.docqueue_id
JOIN docqueues dqsrc ON dqsrc.id = src.docqueue_id
--LEFT JOIN drcarticles da ON da.id = a.drcarticle_id
JOIN vatindexes vi ON vi.id = a.vatindex_id
WHERE
(b.VATByPayment = 'N')
AND a.vatindex_id IS NOT NULL
AND (b.vatdocument='A')
--AND (b.simplifiedVATDocument<>'A')
AND (a.vatmode = 0)
AND (a.vatrate > 0)
AND (b.tradetype<>4)
AND (b.vatdate$date >= :avatdatefrom AND b.vatdate$date < :avatdateto)
AND (b.vatcountry_id = :avatcountry_id)
--AND a.drcarticle_id IS NULL
AND ((:afirmselid = ''
OR (b.firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid))
OR (b.firm_id IN (SELECT id FROM firms WHERE firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid)))
))
INTO
:documenttype, :doc_id, :firm_id, :vatidentnumber, :vatdate$date,
:docnumber, :source_id, :sourcedoctype, :sourcedocnumber, :vatrate, :baseamount, :vatamount, :allowanceamount,
:drc_id, :drccode, :drcquantity, :drcqunit, :mVATIndex_ID,
:mincome, :mVATReportPreference, :SortDocNumber,
:mdivision_id, :mbusorder_id, :mbustransaction_id, :mbusproject_id, :VarSymbol
DO BEGIN
EXECUTE PROCEDURE checkdivisionandbusx
:mdivision_id, :adivisionsselid, :adivisionswithchilds,
:mbusorder_id, :abusordersselid, :abusorderswithchilds,
:mbustransaction_id, :abustransactionsselid, :abustransactionswithchilds,
:mbusproject_id, :abusprojectsselid, :abusprojectswithchilds
RETURNING_VALUES
:msuspend;
IF (msuspend > 0) THEN
BEGIN
SELECT MAX(ID), COALESCE(MAX(TaxLineNumber),''), COALESCE(MAX(VATReportMode),'')
FROM VATSummaryDefinitions2
WHERE (vatindex_id = :mvatindex_id AND parent_id=:AVATDefinitionID)
INTO :definitionrow_id, :taxlinenumber, :vatreportmode;
sectionid = :msectionid;
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
IF (taxlinenumber <> '') THEN
BEGIN
allowanceamount = vatamount;
END
SUSPEND;
END
END
-- *****************************************************************************
/* radky VV */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
b.vatadmitdate$date vatdate$date,
b.externalnumber DocNumber,
b.source_id Source_ID,
dqsrc.documenttype SourceDocType,
src.externalnumber SourceDocNumber,
a.VATRate VATRate,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN (-1*a.LocalTAmountWithoutVAT)
ELSE (-1*a.TAmountWithoutVAT)
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN (-1*(a.LocalTAmount-a.LocalTAmountWithoutVAT))
ELSE (-1*(a.TAmount - a.TAmountWithoutVAT))
END VATAmount,
0 AllowanceAmount,
NULL DRC_ID, '' DRCCode, 0 DRCQuantity, '' DRCQUnit,
a.VATIndex_ID VATIndex_ID,
vi.income Income,
B.VATReportPreference VATReportPreference,
dq.Code || '-' || IB_LPad(CAST(b.OrdNumber AS VARCHAR(8)), 8, '0') || '/' || pe.Code SortDocNumber,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID
FROM
refundedcashpaid2 a
JOIN refundedcashpaid b ON b.id = a.parent_id
JOIN cashpaid src ON src.id = b.source_id
JOIN firms f ON f.id = b.firm_id
JOIN periods pe ON pe.id = b.period_id
JOIN periods pesrc ON pesrc.id = src.period_id
JOIN docqueues dq ON dq.id = b.docqueue_id
JOIN docqueues dqsrc ON dqsrc.id = src.docqueue_id
--LEFT JOIN drcarticles da ON da.id = a.drcarticle_id
JOIN vatindexes vi ON vi.id = a.vatindex_id
WHERE
a.vatindex_id IS NOT NULL
AND (b.vatdocument='A')
--AND (b.simplifiedVATDocument<>'A')
AND (a.vatmode = 0)
AND (a.vatrate > 0)
AND (b.tradetype<>4)
AND (b.vatdate$date >= :avatdatefrom AND b.vatdate$date < :avatdateto)
AND (b.vatcountry_id = :avatcountry_id)
--AND a.drcarticle_id IS NULL
--AND b.isfiscalized<>'A'
AND ((:afirmselid = ''
OR (b.firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid))
OR (b.firm_id IN (SELECT id FROM firms WHERE firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid)))
))
INTO
:documenttype, :doc_id, :firm_id, :vatidentnumber, :vatdate$date,
:docnumber, :source_id, :sourcedoctype, :sourcedocnumber, :vatrate, :baseamount, :vatamount, :allowanceamount,
:drc_id, :drccode, :drcquantity, :drcqunit, :mVATIndex_ID,
:mincome, :mVATReportPreference, :SortDocNumber,
:mdivision_id, :mbusorder_id, :mbustransaction_id, :mbusproject_id
DO BEGIN
EXECUTE PROCEDURE checkdivisionandbusx
:mdivision_id, :adivisionsselid, :adivisionswithchilds,
:mbusorder_id, :abusordersselid, :abusorderswithchilds,
:mbustransaction_id, :abustransactionsselid, :abustransactionswithchilds,
:mbusproject_id, :abusprojectsselid, :abusprojectswithchilds
RETURNING_VALUES
:msuspend;
IF (msuspend > 0) THEN
BEGIN
SELECT MAX(ID), COALESCE(MAX(TaxLineNumber),''), COALESCE(MAX(VATReportMode),'')
FROM VATSummaryDefinitions2
WHERE (vatindex_id = :mvatindex_id AND parent_id=:AVATDefinitionID)
INTO :definitionrow_id, :taxlinenumber, :vatreportmode;
sectionid = :msectionid;
VarSymbol = '';
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
IF (taxlinenumber <> '') THEN
BEGIN
allowanceamount = vatamount;
END
SUSPEND;
END
END
-- *****************************************************************************
/* radky DDZP */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
b.vatadmitdate$date vatdate$date,
b.externalnumber DocNumber,
b.source_id Source_ID,
dqsrc.documenttype SourceDocType,
src.externalnumber SourceDocNumber,
a.VATRate VATRate,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN (-1*a.LocalTAmountWithoutVAT)
ELSE (-1*a.TAmountWithoutVAT)
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN (-1*(a.LocalTAmount-a.LocalTAmountWithoutVAT))
ELSE (-1*(a.TAmount - a.TAmountWithoutVAT))
END VATAmount,
0 AllowanceAmount,
NULL DRC_ID, '' DRCCode, 0 DRCQuantity, '' DRCQUnit,
a.VATIndex_ID VATIndex_ID,
vi.income Income,
B.VATReportPreference VATReportPreference,
dq.Code || '-' || IB_LPad(CAST(b.OrdNumber AS VARCHAR(8)), 8, '0') || '/' || pe.Code SortDocNumber,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID,
B.VarSymbol VarSymbol
FROM
vatreceiveddcreditnotes2 a
JOIN vatreceiveddcreditnotes b ON b.id = a.parent_id
JOIN vatreceiveddinvoices src ON src.id = b.source_id
JOIN firms f ON f.id = b.firm_id
JOIN periods pe ON pe.id = b.period_id
JOIN periods pesrc ON pesrc.id = src.period_id
JOIN docqueues dq ON dq.id = b.docqueue_id
JOIN docqueues dqsrc ON dqsrc.id = src.docqueue_id
--LEFT JOIN drcarticles da ON da.id = a.drcarticle_id
JOIN vatindexes vi ON vi.id = a.vatindex_id
WHERE
(b.VATByPayment = 'N')
AND a.vatindex_id IS NOT NULL
--AND (b.vatdocument='A')
--AND (b.simplifiedVATDocument<>'A')
AND (a.vatmode = 0)
AND (a.vatrate > 0)
AND (b.tradetype<>4)
AND (b.vatdate$date >= :avatdatefrom AND b.vatdate$date < :avatdateto)
AND (b.vatcountry_id = :avatcountry_id)
--AND a.drcarticle_id IS NULL
AND ((:afirmselid = ''
OR (b.firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid))
OR (b.firm_id IN (SELECT id FROM firms WHERE firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid)))
))
INTO
:documenttype, :doc_id, :firm_id, :vatidentnumber, :vatdate$date,
:docnumber, :source_id, :sourcedoctype, :sourcedocnumber, :vatrate, :baseamount, :vatamount, :allowanceamount,
:drc_id, :drccode, :drcquantity, :drcqunit, :mVATIndex_ID,
:mincome, :mVATReportPreference, :SortDocNumber,
:mdivision_id, :mbusorder_id, :mbustransaction_id, :mbusproject_id, :VarSymbol
DO BEGIN
EXECUTE PROCEDURE checkdivisionandbusx
:mdivision_id, :adivisionsselid, :adivisionswithchilds,
:mbusorder_id, :abusordersselid, :abusorderswithchilds,
:mbustransaction_id, :abustransactionsselid, :abustransactionswithchilds,
:mbusproject_id, :abusprojectsselid, :abusprojectswithchilds
RETURNING_VALUES
:msuspend;
IF (msuspend > 0) THEN BEGIN
SELECT MAX(ID), COALESCE(MAX(TaxLineNumber),''), COALESCE(MAX(VATReportMode),'')
FROM VATSummaryDefinitions2
WHERE (vatindex_id = :mvatindex_id AND parent_id=:AVATDefinitionID)
INTO :definitionrow_id, :taxlinenumber, :vatreportmode;
sectionid = :msectionid;
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
IF (taxlinenumber <> '') THEN
BEGIN
allowanceamount = vatamount;
END
SUSPEND;
END
END
-- *****************************************************************************
/* radky DRC pro DP, VV, DDP */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
b.vatdate$date vatdate$date,
dq.code || '-' || CAST(b.ordnumber AS VARCHAR(10)) || '/' || pe.code DocNumber,
b.sdocument_id Source_ID,
b.sdocumenttype SourceDocType,
'' SourceDocNumber,
a.VATRate VATRate,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN (-1*a.LocalBaseAmount)
ELSE (-1*a.BaseAmount)
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN (-1*a.LocalVATAmount)
ELSE (-1*a.VATAmount)
END VATAmount,
0 AllowanceAmount,
NULL DRC_ID, '' DRCCode, 0 DRCQuantity, '' DRCQUnit,
a.VATIndex_ID VATIndex_ID,
vi.income Income,
A.vatindex_id VATIndex_ID,
vi.allowancevatindex_id AllowanceVATIndex_ID,
b.vatdeductiondate$date VATDeductionDate,
B.VATReportPreference VATReportPreference,
dq.Code || '-' || IB_LPad(CAST(b.OrdNumber AS VARCHAR(8)), 8, '0') || '/' || pe.Code SortDocNumber,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID
FROM
reversechargedeclarations2 a
JOIN reversechargedeclarations b ON b.id = a.parent_id
JOIN firms f ON f.id = b.firm_id
JOIN periods pe ON pe.id = b.period_id
JOIN docqueues dq ON dq.id = b.docqueue_id
-- LEFT JOIN drcarticles da ON da.id = a.drcarticle_id
JOIN vatindexes vi ON vi.id = a.vatindex_id
WHERE
((b.sdocumenttype = '61') OR (b.sdocumenttype = '08') OR (b.sdocumenttype = '65') )
AND a.vatindex_id IS NOT NULL
AND b.vatcountry_id=:avatcountry_id
AND (
((b.vatdate$date >= :avatdatefrom) AND (b.vatdate$date < :avatdateto)) OR
(vi.allowancevatindex_id IS NOT NULL AND (b.vatdeductiondate$date >= :avatdatefrom) AND (b.vatdeductiondate$date < :avatdateto))
)
AND ((:afirmselid = ''
OR (b.firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid))
OR (b.firm_id IN (SELECT id FROM firms WHERE firm_id IN (SELECT obj_id FROM seldat WHERE sel_id = :afirmselid)))
))
INTO
:documenttype, :doc_id, :firm_id, :vatidentnumber, :vatdate$date,
:docnumber, :source_id, :sourcedoctype, :sourcedocnumber, :vatrate, :baseamount, :vatamount, :allowanceamount,
:drc_id, :drccode, :drcquantity, :drcqunit, :mVATIndex_ID,
:mincome, :mvatindex_id, :mallowancevatindex_id, :mvatdeductiondate, :mVATReportPreference, :SortDocNumber,
:mdivision_id, :mbusorder_id, :mbustransaction_id, :mbusproject_id
DO BEGIN
EXECUTE PROCEDURE checkdivisionandbusx
:mdivision_id, :adivisionsselid, :adivisionswithchilds,
:mbusorder_id, :abusordersselid, :abusorderswithchilds,
:mbustransaction_id, :abustransactionsselid, :abustransactionswithchilds,
:mbusproject_id, :abusprojectsselid, :abusprojectswithchilds
RETURNING_VALUES
:msuspend;
IF (msuspend > 0) THEN BEGIN
SELECT MAX(ID) FROM VATSummaryDefinitions2 WHERE vatindex_id=:mVATIndex_ID AND parent_id=:AVATDefinitionID INTO :DefinitionRow_ID;
SELECT COALESCE(MAX(TaxLineNumber),''), COALESCE(MAX(VATReportMode),'')
FROM VATSummaryDefinitions2
WHERE (vatindex_id = :mAllowanceVATIndex_ID AND parent_id=:AVATDefinitionID)
INTO :taxlinenumber, :vatreportmode;
sectionid = :msectionid;
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
-- DRC doklad se prevede na zdrojovy doklad DRC -> DP, sourcedoc bude pak zdroj DP
doc_id = source_id;
documenttype = sourcedoctype;
VarSymbol = '';
IF (:sourcedoctype='61') THEN
BEGIN
SELECT rci.id, dq.documenttype, rcn.externalnumber, rci.externalnumber, rcn.VATAdmitDate$DATE, rcn.VarSymbol
FROM
receivedcreditnotes rcn
JOIN receivedinvoices rci ON rci.id=rcn.source_id
JOIN docqueues dq ON dq.id=rci.docqueue_id
WHERE
rcn.id=:source_id
INTO
:source_id, :sourcedoctype, :docnumber, :sourcedocnumber, :mVATADmitDate, :VarSymbol;
END
ELSE
IF (:sourcedoctype='08') THEN
BEGIN
VarSymbol = '';
SELECT rci.id, dq.documenttype, rcn.externalnumber, rci.externalnumber, rcn.VATAdmitDate$DATE
FROM
refundedcashpaid rcn
JOIN cashpaid rci ON rci.id=rcn.source_id
JOIN docqueues dq ON dq.id=rci.docqueue_id
WHERE
rcn.id=:source_id
INTO
:source_id, :sourcedoctype, :docnumber, :sourcedocnumber, :mVATADmitDate;
END
ELSE
IF (:sourcedoctype='65') THEN
BEGIN
SELECT rci.id, dq.documenttype, rcn.externalnumber, rci.externalnumber, rcn.VATAdmitDate$DATE, rcn.VarSymbol
FROM
vatreceiveddcreditnotes rcn
JOIN vatreceiveddinvoices rci ON rci.id=rcn.source_id
JOIN docqueues dq ON dq.id=rci.docqueue_id
WHERE
rcn.id=:source_id
INTO
:source_id, :sourcedoctype, :docnumber, :sourcedocnumber, :mVATADmitDate, :VarSymbol;
END
sourcedocnumber = COALESCE (:sourcedocnumber, '');
allowanceamount = 0;
IF ((mallowancevatindex_id IS NOT NULL) AND
(mvatdeductiondate >= :avatdatefrom AND mvatdeductiondate < :avatdateto))
THEN BEGIN
allowanceamount = :vatamount;
END
IF ((mallowancevatindex_id IS NOT NULL) AND
((vatdate$date < :avatdatefrom) OR (vatdate$date >= :avatdateto)))
THEN BEGIN
if (taxlinenumber = '') then
definitionrow_id = NULL;
END
-- datum se bere z DRC dokladu
--vatdate$date = :mVATAdmitDate;
SUSPEND;
END
END
-- *****************************************************************************
END