BEGIN
SELECT country_id FROM globdata INTO :mLocalCountry_ID;
IF (mLocalCountry_ID = '') THEN
mLocalCountry_ID = :AVATCountry_ID;
SELECT Code FROM Countries WHERE ID = :AVATCountry_ID INTO :mVATCountryCode;
IF (mVATCountryCode IS NULL) THEN
BEGIN
SELECT Min(C.Code) FROM Globdata G JOIN Countries C ON C.ID = G.Country_ID INTO :mVATCountryCode;
END
/* DPH z plateb je jen v SK */
IF (mVATCountryCode <> 'SK') THEN
EXIT;
/* Zde se bere v uvahu pouze VATMode = 0 */
CorrectionCode = '';
-- *****************************************************************************
/* FV - 1 */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
P.vatdate$date VATDate$DATE,
CASE b.externalnumber
WHEN '' THEN CAST(dq.code || '-' || CAST(b.ordnumber AS VARCHAR(10)) || '/' || pe.code AS VARCHAR(30))
ELSE b.ExternalNumber
END DocNumber,
NULL Source_ID,
'' SourceDocType,
b.VATReportReference SourceDocNumber,
a.VATRate VATRate,
CASE :mLocalCountry_ID
WHEN :AVatCountry_ID
THEN P.LocalTAmountWithoutVAT
ELSE P.TAmountWithoutVAT
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVatCountry_ID
THEN P.LocalTAmount-P.LocalTAmountWithoutVAT
ELSE P.TAmount - P.TAmountWithoutVAT
END VATAmount,
0 AllowanceAmount,
A.drcarticle_id DRC_ID, COALESCE(da.code,'') DRCCode, A.drcquantity DRCQuantity, A.drcqunit DRCQUnit,
a.VATIndex_ID VATIndex_ID,
A.rowtype RowType,
vi.income Income,
A.quantity Quantity,
B.VATReportPreference VATReportPreference,
CAST(dq.Code || '-' || IB_LPad(CAST(b.OrdNumber AS VARCHAR(8)), 8, '0') || '/' || pe.Code AS VARCHAR(22)) SortDocNumber,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID,
B.SimplifiedVATDocument SimplifiedVATDocument,
B.VarSymbol VarSymbol
FROM
Payments P
join IssuedInvoices2 A on A.ID = P.Row_ID
join IssuedInvoices 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
join VATIndexes VI on VI.ID = A.VATIndex_ID
LEFT JOIN drcarticles da ON da.id = a.drcarticle_id
WHERE
(P.PDocumentType = '03')
AND (B.VATByPayment = 'A')
AND a.vatindex_id IS NOT NULL
AND (b.vatdocument='A')
AND (a.vatmode = 0)
AND (A.VATRate > 0)
AND (a.rowtype IN (1,2,3,4,5))
AND (b.TradeType <> 7) AND (b.TradeType <> 8)
AND (P.vatdate$date >= :avatdatefrom AND P.vatdate$date < :avatdateto)
AND ((P.VATDate$Date <= B.VATByPaymentEndDate$Date) or (B.VATByPaymentEndDate$Date = 0))
AND (b.vatcountry_id = :avatcountry_id)
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,
:mrowtype, :mincome, :mquantity, :mVATReportPreference, :SortDocNumber,
:mdivision_id, :mbusorder_id, :mbustransaction_id, :mbusproject_id,
:msimplifiedvatdocument, :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) AND ((BaseAmount <> 0) OR (VATAmount <> 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;
IF ((mrowtype = 3) AND (mquantity < 0)) THEN
BEGIN
IF (mincome = 'A') THEN BEGIN --ERR-2721/2009
baseamount = (-1) * baseamount;
vatamount = (-1) * vatamount;
allowanceamount = (-1) * allowanceamount;
END
END
SectionID = '';
IF (msimplifiedvatdocument <> 'A') THEN
BEGIN
SectionID = 'A1';
END
ELSE
BEGIN
SectionID = 'D2';
END
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
if (SectionID <> '') THEN
BEGIN
SUSPEND;
END
END
END
-- *****************************************************************************
/* DV - 2 */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
P.VATDate$Date VATDate$Date,
CASE b.externalnumber
WHEN '' THEN CAST(dq.code || '-' || CAST(b.ordnumber AS VARCHAR(10)) || '/' || pe.code AS VARCHAR(30))
ELSE b.ExternalNumber
END DocNumber,
b.source_id Source_ID,
dqsrc.documenttype SourceDocType,
dqsrc.code || '-' || CAST(src.ordnumber AS VARCHAR(10)) || '/' || pesrc.code SourceDocNumber,
CASE A.VATMode
WHEN 1 THEN 0
ELSE a.VATRate
END VATRate,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN (-1*P.LocalTAmountWithoutVAT)
ELSE (-1*P.TAmountWithoutVAT)
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN (-1*(P.LocalTAmount-P.LocalTAmountWithoutVAT))
ELSE (-1*(P.TAmount - P.TAmountWithoutVAT))
END VATAmount,
0 AllowanceAmount,
A.drcarticle_id DRC_ID, COALESCE(da.code,'') DRCCode, (-1*A.drcquantity) DRCQuantity, A.drcqunit DRCQUnit,
a.VATIndex_ID VATIndex_ID,
A.rowtype RowType,
vi.income Income,
src.externalnumber ExternalNumber,
A.quantity Quantity,
B.VATReportPreference VATReportPreference,
CAST(dq.Code || '-' || IB_LPad(CAST(b.OrdNumber AS VARCHAR(8)), 8, '0') || '/' || pe.Code AS VARCHAR(22)) SortDocNumber,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID,
B.SimplifiedVATDocument SimplifiedVATDocument,
B.VarSymbol VarSymbol
FROM
Payments P
JOIN issuedcreditnotes2 a on A.ID = P.Row_ID
JOIN issuedcreditnotes b ON b.id = a.parent_id
JOIN issuedinvoices src ON src.id = b.source_id
-- LEFT JOIN creditnotesacknowledges cna ON cna.creditnote_id = b.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
(P.PDocumentType = '60')
AND (B.VATByPayment = 'A')
AND a.vatindex_id IS NOT NULL
AND (b.vatdocument='A')
AND (a.vatmode = 0)
AND (A.VATRate > 0)
AND (a.rowtype IN (1,2,3,4,5))
AND (b.TradeType <> 7) AND (b.TradeType <> 8)
AND (P.vatdate$date >= :avatdatefrom AND P.vatdate$date < :avatdateto)
AND ((P.VATDate$Date <= b.VATByPaymentEndDate$Date) or (b.VATByPaymentEndDate$Date = 0))
AND (b.vatcountry_id = :avatcountry_id)
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,
:mrowtype, :mincome, :mexternalnumber, :mquantity, :mVATReportPreference, :SortDocNumber,
:mdivision_id, :mbusorder_id, :mbustransaction_id, :mbusproject_id,
:msimplifiedvatdocument, :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) AND ((BaseAmount <> 0) OR (VATAmount <> 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;
IF (mexternalnumber <> '') THEN
BEGIN
sourcedocnumber = :mexternalnumber;
END
SectionID = '';
IF (msimplifiedvatdocument <> 'A') THEN
BEGIN
SectionID = 'C1';
END
ELSE
BEGIN
SectionID = 'D2';
END
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
if (SectionID <> '') THEN
BEGIN
SUSPEND;
END
END
END
-- *****************************************************************************
/* FP - 3 */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
P.vatdate$date vatdate$date,
b.externalnumber DocNumber,
NULL Source_ID,
'' SourceDocType,
b.VATReportReference SourceDocNumber,
a.VATRate VATRate,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN P.LocalTAmountWithoutVAT
ELSE P.TAmountWithoutVAT
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN P.LocalTAmount-P.LocalTAmountWithoutVAT
ELSE P.TAmount - P.TAmountWithoutVAT
END VATAmount,
0 AllowanceAmount,
NULL DRC_ID, '' DRCCode, 0 DRCQuantity, '' DRCQUnit,
a.VATIndex_ID VATIndex_ID,
vi.income Income,
B.VATReportPreference VATReportPreference,
CAST(dq.Code || '-' || IB_LPad(CAST(b.OrdNumber AS VARCHAR(8)), 8, '0') || '/' || pe.Code AS VARCHAR(22)) SortDocNumber,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID,
B.SimplifiedVATDocument SimplifiedVATDocument,
B.VarSymbol VarSymbol
FROM
Payments P
JOIN receivedinvoices2 A on A.ID = P.Row_ID
JOIN receivedinvoices 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
(P.PDocumentType = '04')
AND (B.VATByPayment = 'A')
AND (b.vatdocument='A')
AND a.vatindex_id IS NOT NULL
AND (a.vatmode = 0)
AND (a.vatrate > 0)
AND (b.tradetype<>4)
AND (P.vatdate$date >= :avatdatefrom AND P.vatdate$date < :avatdateto)
AND ((P.VATDate$Date <= b.VATByPaymentEndDate$Date) or (b.VATByPaymentEndDate$Date = 0))
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,
:msimplifiedvatdocument, :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) AND ((BaseAmount <> 0) OR (VATAmount <> 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;
IF (taxlinenumber <> '') THEN
BEGIN
allowanceamount = vatamount;
END
SectionID = '';
IF (msimplifiedvatdocument <> 'A') THEN
BEGIN
SectionID = 'B2';
END
ELSE
BEGIN
SectionID = 'B3';
END
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
if (SectionID <> '') THEN
BEGIN
SUSPEND;
END
END
END
-- *****************************************************************************
/* DP - 4 */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
P.vatdate$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*P.LocalTAmountWithoutVAT)
ELSE (-1*P.TAmountWithoutVAT)
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN (-1*(P.LocalTAmount-P.LocalTAmountWithoutVAT))
ELSE (-1*(P.TAmount - P.TAmountWithoutVAT))
END VATAmount,
0 AllowanceAmount,
NULL DRC_ID, '' DRCCode, 0 DRCQuantity, '' DRCQUnit,
a.VATIndex_ID VATIndex_ID,
vi.income Income,
B.VATReportPreference VATReportPreference,
CAST(dq.Code || '-' || IB_LPad(CAST(b.OrdNumber AS VARCHAR(8)), 8, '0') || '/' || pe.Code AS VARCHAR(22)) 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
Payments P
JOIN receivedcreditnotes2 a on A.ID = P.Row_ID
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
(P.PDocumentType = '61')
AND (B.VATByPayment = 'A')
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 (P.vatdate$date >= :avatdatefrom AND P.vatdate$date < :avatdateto)
AND ((P.VATDate$Date <= b.VATByPaymentEndDate$Date) or (b.VATByPaymentEndDate$Date = 0))
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) AND ((BaseAmount <> 0) OR (VATAmount <> 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;
IF (taxlinenumber <> '') THEN
BEGIN
allowanceamount = vatamount;
END
SectionID = 'C2';
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
if (SectionID <> '') THEN
BEGIN
SUSPEND;
END
END
END
-- *****************************************************************************
/* DDZV - 5 */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
P.vatdate$date VATDate$DATE,
CASE b.externalnumber
WHEN '' THEN CAST(dq.code || '-' || CAST(b.ordnumber AS VARCHAR(10)) || '/' || pe.code AS VARCHAR(30))
ELSE b.ExternalNumber
END DocNumber,
b.source_id Source_ID,
dqsrc.documenttype SourceDocType,
dqsrc.code || '-' || CAST(src.ordnumber AS VARCHAR(10)) || '/' || pesrc.code SourceDocNumber,
CASE A.VATMode
WHEN 1 THEN 0
ELSE a.VATRate
END VATRate,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN (-1*P.LocalTAmountWithoutVAT)
ELSE (-1*P.TAmountWithoutVAT)
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN (-1*(P.LocalTAmount-P.LocalTAmountWithoutVAT))
ELSE (-1*(P.TAmount - P.TAmountWithoutVAT))
END VATAmount,
0 AllowanceAmount,
A.drcarticle_id DRC_ID, COALESCE(da.code,'') DRCCode, (-1*A.drcquantity) DRCQuantity, A.drcqunit DRCQUnit,
a.VATIndex_ID VATIndex_ID,
A.rowtype RowType,
vi.income Income,
src.externalnumber ExternalNumber,
0 Quantity,
B.VATReportPreference VATReportPreference,
CAST(dq.Code || '-' || IB_LPad(CAST(b.OrdNumber AS VARCHAR(8)), 8, '0') || '/' || pe.Code AS VARCHAR(22)) SortDocNumber,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID,
B.SimplifiedVATDocument SimplifiedVATDocument,
B.VarSymbol VarSymbol
FROM
Payments P
JOIN vatissueddcreditnotes2 a on A.ID = P.Row_ID
JOIN vatissueddcreditnotes b ON b.id = a.parent_id
JOIN vatissueddinvoices 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
(P.PDocumentType = '63')
AND (B.VATByPayment = 'A')
AND a.vatindex_id IS NOT NULL
AND (a.vatmode = 0)
AND (A.VATRate > 0)
AND (a.rowtype IN (1,2,3,4,5))
AND (b.TradeType <> 7) AND (b.TradeType <> 8)
AND (P.vatdate$date >= :avatdatefrom AND P.vatdate$date < :avatdateto)
AND ((P.VATDate$Date <= b.VATByPaymentEndDate$Date) or (b.VATByPaymentEndDate$Date = 0))
AND (b.vatcountry_id = :avatcountry_id)
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,
:mrowtype, :mincome, :mexternalnumber, :mquantity, :mVATReportPreference, :SortDocNumber,
:mdivision_id, :mbusorder_id, :mbustransaction_id, :mbusproject_id,
:msimplifiedvatdocument, :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) AND ((BaseAmount <> 0) OR (VATAmount <> 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;
IF (mexternalnumber <> '') THEN
BEGIN
sourcedocnumber = :mexternalnumber;
END
IF (taxlinenumber <> '') THEN
BEGIN
allowanceamount = vatamount;
END
SectionID = '';
IF (msimplifiedvatdocument <> 'A') THEN
BEGIN
SectionID = 'C1';
END
ELSE
BEGIN
SectionID = 'D2';
END
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
if (SectionID <> '') THEN
BEGIN
SUSPEND;
END
END
END
-- *****************************************************************************
/* DDZP - 6 */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
P.vatdate$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*P.LocalTAmountWithoutVAT)
ELSE (-1*P.TAmountWithoutVAT)
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN (-1*(P.LocalTAmount-P.LocalTAmountWithoutVAT))
ELSE (-1*(P.TAmount - P.TAmountWithoutVAT))
END VATAmount,
0 AllowanceAmount,
NULL DRC_ID, '' DRCCode, 0 DRCQuantity, '' DRCQUnit,
a.VATIndex_ID VATIndex_ID,
vi.income Income,
B.VATReportPreference VATReportPreference,
CAST(dq.Code || '-' || IB_LPad(CAST(b.OrdNumber AS VARCHAR(8)), 8, '0') || '/' || pe.Code AS VARCHAR(22)) 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
Payments P
JOIN vatreceiveddcreditnotes2 a on A.ID = P.Row_ID
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
JOIN vatindexes vi ON vi.id = a.vatindex_id
WHERE
(P.PDocumentType = '65')
AND (B.VATByPayment = 'A')
AND a.vatindex_id IS NOT NULL
AND (a.vatmode = 0)
AND (a.vatrate > 0)
AND (b.tradetype<>4)
AND (P.vatdate$date >= :avatdatefrom AND P.vatdate$date < :avatdateto)
AND ((P.VATDate$Date <= b.VATByPaymentEndDate$Date) or (b.VATByPaymentEndDate$Date = 0))
AND (b.vatcountry_id = :avatcountry_id)
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) AND ((BaseAmount <> 0) OR (VATAmount <> 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;
IF (taxlinenumber <> '') THEN
BEGIN
allowanceamount = vatamount;
END
SectionID = 'C2';
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
if (SectionID <> '') THEN
BEGIN
SUSPEND;
END
END
END
-- *****************************************************************************
IF (AVATCountry_ID = mLocalCountry_ID) THEN
BEGIN
/* OSP - 7 */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
P.vatdate$date VATDate$DATE,
CASE b.externalnumber
WHEN '' THEN CAST(dq.code || '-' || CAST(b.ordnumber AS VARCHAR(10)) || '/' || pe.code AS VARCHAR(30))
ELSE b.ExternalNumber
END DocNumber,
NULL Source_ID,
'' SourceDocType,
b.VATReportReference SourceDocNumber,
a.VATRate VATRate,
CASE :mLocalCountry_ID
WHEN :AVatCountry_ID
THEN P.LocalTAmountWithoutVAT
ELSE P.TAmountWithoutVAT
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVatCountry_ID
THEN P.LocalTAmount-P.LocalTAmountWithoutVAT
ELSE P.TAmount - P.TAmountWithoutVAT
END VATAmount,
0 AllowanceAmount,
NULL DRC_ID, '' DRCCode, 0 DRCQuantity, '' DRCQUnit,
a.VATIndex_ID VATIndex_ID,
0 RowType,
vi.income Income,
0 Quantity,
B.VATReportPreference VATReportPreference,
CAST(dq.Code || '-' || IB_LPad(CAST(b.OrdNumber AS VARCHAR(8)), 8, '0') || '/' || pe.Code AS VARCHAR(22)) SortDocNumber,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID,
B.SimplifiedVATDocument SimplifiedVATDocument,
B.VarSymbol VarSymbol
FROM
Payments P
JOIN otherincomes2 A on A.ID = P.Row_ID
JOIN otherincomes 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
JOIN vatindexes vi ON vi.id = a.vatindex_id
WHERE
(P.PDocumentType = '01') AND
B.VATByPayment = 'A' AND
a.vatindex_id IS NOT NULL
AND (b.vatdocument='A')
AND (a.vatrate > 0)
AND (P.vatdate$date >= :avatdatefrom AND P.vatdate$date < :avatdateto)
AND ((P.VATDate$Date <= b.VATByPaymentEndDate$Date) or (b.VATByPaymentEndDate$Date = 0))
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,
:mrowtype, :mincome, :mquantity, :mVATReportPreference, :SortDocNumber,
:mdivision_id, :mbusorder_id, :mbustransaction_id, :mbusproject_id,
:msimplifiedvatdocument, :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) AND ((BaseAmount <> 0) OR (VATAmount <> 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 = '';
IF (msimplifiedvatdocument <> 'A') THEN
BEGIN
SectionID = 'A1';
END
ELSE
BEGIN
SectionID = 'D2';
END
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
if (SectionID <> '') THEN
BEGIN
SUSPEND;
END
END
END
-- *****************************************************************************
/* OSV - 8 */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
P.vatdate$date vatdate$date,
CASE b.externalnumber
WHEN '' THEN dq.code || '-' || CAST(b.ordnumber AS VARCHAR(10)) || '/' || pe.code
ELSE b.ExternalNumber
END DocNumber,
NULL Source_ID,
'' SourceDocType,
b.VATReportReference SourceDocNumber,
a.VATRate VATRate,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN P.LocalTAmountWithoutVAT
ELSE P.TAmountWithoutVAT
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN P.LocalTAmount-P.LocalTAmountWithoutVAT
ELSE P.TAmount - P.TAmountWithoutVAT
END VATAmount,
0 AllowanceAmount,
NULL DRC_ID, '' DRCCode, 0 DRCQuantity, '' DRCQUnit,
a.VATIndex_ID VATIndex_ID,
vi.income Income,
B.VATReportPreference VATReportPreference,
CAST(dq.Code || '-' || IB_LPad(CAST(b.OrdNumber AS VARCHAR(8)), 8, '0') || '/' || pe.Code AS VARCHAR(22)) SortDocNumber,
A.division_id Division_ID, A.busorder_id BusOrder_ID, A.bustransaction_id BusTransaction_ID, A.busproject_id BusProject_ID,
B.SimplifiedVATDocument SimplifiedVATDocument,
B.VarSymbol VarSymbol
FROM
Payments P
JOIN otherexpenses2 A on A.ID = P.Row_ID
JOIN otherexpenses 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
JOIN vatindexes vi ON vi.id = a.vatindex_id
WHERE
(P.PDocumentType = '02') AND
B.VATByPayment = 'A' AND
a.vatindex_id IS NOT NULL
AND (b.vatdocument='A')
AND (a.vatrate > 0)
AND (P.vatdate$date >= :avatdatefrom AND P.vatdate$date < :avatdateto)
AND ((P.VATDate$Date <= b.VATByPaymentEndDate$Date) or (b.VATByPaymentEndDate$Date = 0))
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,
:msimplifiedvatdocument, :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) AND ((BaseAmount <> 0) OR (VATAmount <> 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;
IF (taxlinenumber <> '') THEN
BEGIN
allowanceamount = vatamount;
END
SectionID = '';
IF (msimplifiedvatdocument <> 'A') THEN
BEGIN
SectionID = 'B2';
END
ELSE
BEGIN
SectionID = 'B3';
END
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
if (SectionID <> '') THEN
BEGIN
SUSPEND;
END
END
END
END /* LocalCountry */
-- *****************************************************************************
END