BEGIN
-- *****************************************************************************
-- Zjednodusene doklady, VATMode=0, nefiskalizovane **
-- *****************************************************************************
mSectionID = 'D2';
SELECT country_id FROM globdata INTO :mLocalCountry_ID;
IF (mLocalCountry_ID = '') THEN
mLocalCountry_ID = :AVATCountry_ID;
-- *****************************************************************************
/* radky FV */
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,
NULL Source_ID,
'' SourceDocType,
b.VATReportReference SourceDocNumber,
a.VATRate VATRate,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN a.LocalTAmountWithoutVAT
ELSE a.TAmountWithoutVAT
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN a.LocalTAmount-a.LocalTAmountWithoutVAT
ELSE a.TAmount - a.TAmountWithoutVAT
END VATAmount,
0 AllowanceAmount,
NULL DRC_ID, '' DRCCode, 0 DRCQuantity, '' DRCQUnit,
a.VATIndex_ID VATIndex_ID,
A.rowtype RowType,
vi.income Income,
b.externalnumber ExternalNumber,
A.quantity Quantity,
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
issuedinvoices2 a
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
--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 (a.rowtype IN (1,2,3,4,5))
AND (b.TradeType <> 7) AND (b.TradeType <> 8)
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,
:mrowtype, :mincome, :mexternalnumber, :mquantity, :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 ((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
IF (mexternalnumber <> '') THEN
BEGIN
docnumber = :mexternalnumber;
END
SUSPEND;
END
END
-- *****************************************************************************
/* radky PP */
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,
NULL Source_ID,
'' SourceDocType,
b.VATReportReference SourceDocNumber,
a.VATRate VATRate,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN a.LocalTAmountWithoutVAT
ELSE a.TAmountWithoutVAT
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN a.LocalTAmount-a.LocalTAmountWithoutVAT
ELSE a.TAmount - a.TAmountWithoutVAT
END VATAmount,
0 AllowanceAmount,
NULL DRC_ID, '' DRCCode, 0 DRCQuantity, '' DRCQUnit,
a.VATIndex_ID VATIndex_ID,
A.rowtype RowType,
vi.income Income,
b.externalnumber ExternalNumber,
A.quantity Quantity,
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
cashreceived2 a
JOIN cashreceived 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
a.vatindex_id IS NOT NULL
AND (b.vatdocument='A')
AND (b.simplifiedvatdocument='A')
AND (a.vatmode = 0)
AND (a.vatrate > 0)
AND (a.rowtype IN (1,2,3,4,5))
AND (b.isfiscalized<>'A')
AND (b.IsExternFiscal<>'A')
AND (b.TradeType <> 7) AND (b.TradeType <> 8)
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,
:mrowtype, :mincome, :mexternalnumber, :mquantity, :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;
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
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
IF (mexternalnumber <> '') THEN
BEGIN
docnumber = :mexternalnumber;
END
VarSymbol = '';
SUSPEND;
END
END
-- *****************************************************************************
/* radky DZV */
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,
NULL Source_ID,
'' SourceDocType,
b.VATReportReference SourceDocNumber,
a.VATRate VATRate,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN a.LocalTAmountWithoutVAT
ELSE a.TAmountWithoutVAT
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN a.LocalTAmount-a.LocalTAmountWithoutVAT
ELSE a.TAmount - a.TAmountWithoutVAT
END VATAmount,
0 AllowanceAmount,
NULL DRC_ID, '' DRCCode, 0 DRCQuantity, '' DRCQUnit,
a.VATIndex_ID VATIndex_ID,
A.rowtype RowType,
vi.income Income,
b.externalnumber ExternalNumber,
0 Quantity,
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
vatissueddinvoices2 a
JOIN vatissueddinvoices 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
a.vatindex_id IS NOT NULL
--AND (b.vatdocument='A')
AND (b.simplifiedvatdocument='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 (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,
:mrowtype, :mincome, :mexternalnumber, :mquantity, :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;
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
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
IF (mexternalnumber <> '') THEN
BEGIN
docnumber = :mexternalnumber;
END
VarSymbol = '';
SUSPEND;
END
END
-- *****************************************************************************
/* radky OSP */
IF (AVATCountry_ID = mLocalCountry_ID) THEN
BEGIN
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
b.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 a.LocalTAmountWithoutVAT
ELSE a.TAmountWithoutVAT
END BaseAmount,
CASE :mLocalCountry_ID
WHEN :AVATCountry_ID
THEN a.LocalTAmount-a.LocalTAmountWithoutVAT
ELSE a.TAmount - a.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,
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
otherincomes2 a
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
(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 (a.rowtype IN (1,2,3,4,5))
--AND ((b.tradetype=1) OR (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,
:mrowtype, :mincome, :mquantity, :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
SUSPEND;
END
END
END
-- *****************************************************************************
/* radky DV */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
CASE B.Acknowledge
WHEN 2 THEN CNA.DocDate$Date
ELSE B.VATDate$Date
END VATDate$Date,
CASE b.externalnumber
WHEN '' THEN dq.code || '-' || CAST(b.ordnumber AS VARCHAR(10)) || '/' || pe.code
ELSE b.ExternalNumber
END DocNumber,
b.source_id Source_ID,
dqsrc.documenttype SourceDocType,
dqsrc.code || '-' || CAST(src.ordnumber AS VARCHAR(10)) || '/' || pesrc.code 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,
A.rowtype RowType,
vi.income Income,
src.externalnumber ExternalNumber,
A.quantity Quantity,
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
issuedcreditnotes2 a
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
(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 (a.rowtype IN (1,2,3,4,5))
AND (b.TradeType <> 7) AND (b.TradeType <> 8)
AND ( (b.acknowledge = 0 AND (b.vatdate$date >= :avatdatefrom AND b.vatdate$date < :avatdateto)) OR
(b.acknowledge = 2 AND (cna.docdate$date >= :avatdatefrom AND cna.docdate$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,
:mrowtype, :mincome, :mexternalnumber, :mquantity, :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 (mexternalnumber <> '') THEN
BEGIN
sourcedocnumber = :mexternalnumber;
END
SUSPEND;
END
END
-- *****************************************************************************
/* radky VP */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
b.vatdate$date VATDate$DATE,
CASE b.externalnumber
WHEN '' THEN dq.code || '-' || CAST(b.ordnumber AS VARCHAR(10)) || '/' || pe.code
ELSE b.ExternalNumber
END DocNumber,
b.source_id Source_ID,
dqsrc.documenttype SourceDocType,
dqsrc.code || '-' || CAST(src.ordnumber AS VARCHAR(10)) || '/' || pesrc.code 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.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,
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
refundedcashreceived2 a
JOIN refundedcashreceived b ON b.id = a.parent_id
JOIN cashreceived 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 (a.rowtype IN (1,2,3,4,5))
AND (b.isfiscalized<>'A')
AND (b.IsExternFiscal<>'A')
AND (b.TradeType <> 7) AND (b.TradeType <> 8)
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,
:mrowtype, :mincome, :mexternalnumber, :mquantity, :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;
IF (mVATReportPreference <> '') THEN
BEGIN
SectionID = :mVATReportPreference;
END
IF (mexternalnumber <> '') THEN
BEGIN
sourcedocnumber = :mexternalnumber;
END
VarSymbol = '';
SUSPEND;
END
END
-- *****************************************************************************
/* radky DDZV */
FOR
SELECT
dq.documenttype DocumentType,
b.ID Doc_ID,
b.firm_id Firm_ID,
f.vatidentnumber VATIdentNumber,
b.vatdate$date VATDate$DATE,
CASE b.externalnumber
WHEN '' THEN dq.code || '-' || CAST(b.ordnumber AS VARCHAR(10)) || '/' || pe.code
ELSE b.ExternalNumber
END DocNumber,
b.source_id Source_ID,
dqsrc.documenttype SourceDocType,
dqsrc.code || '-' || CAST(src.ordnumber AS VARCHAR(10)) || '/' || pesrc.code 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,
A.rowtype RowType,
vi.income Income,
src.externalnumber ExternalNumber,
0 Quantity,
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
vatissueddcreditnotes2 a
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
(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 (a.rowtype IN (1,2,3,4,5))
AND (b.TradeType <> 7) AND (b.TradeType <> 8)
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,
:mrowtype, :mincome, :mexternalnumber, :mquantity, :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 (mexternalnumber <> '') THEN
BEGIN
sourcedocnumber = :mexternalnumber;
END
SUSPEND;
END
END
-- *****************************************************************************
END