Dynamický dotaz - DPH - Uzávěrky Export CZ KH DPH

ID:

LMFSL2MTLD24JG2Z254ZZYBTUS

Datasety

MAIN

Popis:

DPH - Uzávěrky Export CZ KH DPH

SQL definice:

SELECT {FIELDS} 
FROM 
  VATClosings A
  {JOIN}
{WHERE}
{ORDERBY}

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	EPXJ3GDLIOZ41FPIS3POB3NUCS
C	4J5FINKNYNDL3C5P00CA141B44	C	Země
SUC	22AHIVDVAVE13C5S00CA141B44	SUC	Uživatel
SUU	22AHIVDVAVE13C5S00CA141B44	SUU	Uživatel
VATC	EPXJ3GDLIOZ41FPIS3POB3NUCS	VATC	DPH uzávěrka
VATS	KWM3VUOVEBF4HF0RPMRTW51JUK	VATS	Definice DPH

RowA1

Popis:

Kontrolní výkaz DPH A1

SQL definice:

SELECT
MIN(A.ID) ID,
A.SectionID SectionID,
A.DocumentType DocumentType,
MIN(A.DocNumber) DocNumber,
MIN(A.SourceDocNumber) SourceDocNumber,
A.SortDocNumber SortDocNumber,
MIN(A.VATIdentNumber) VATIdentNumber,
MIN(A.VATDate$DATE) VATDate$DATE,
SUM(A.BaseAmount) zakl_dane1,
MIN(A.Firm_ID) Firm_ID,
MIN(A.VATRate_ID) VATRate_ID,
A.DRCCode DRCCode
FROM
 VATClosings4 A
 JOIN Firms F ON F.ID=A.Firm_ID
 JOIN VATRates VR ON VR.ID=A.VATRate_ID

					
WHERE
 A.Parent_ID = :ID and A.SectionID='A1'
GROUP BY
 A.SectionID, A.DocumentType, A.SortDocNumber, A.DRCCode
ORDER BY
 A.SectionID, A.DocumentType, A.SortDocNumber, A.DRCCode

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	CXUCAWC355R4FBLTTWMTLCPL0S
F	4K3EXM5PQBCL35CH000ILPWJF4	F	Firma
VR	ZSHD5MXH4Z24J0H2Y2R2VUZPEG	VR	DPH sazba

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
zakl_dane1	dtFloat	15			Základ daně

RowA2

Popis:

Kontrolní výkaz DPH A2

SQL definice:

SELECT
MIN(A.ID) ID,
A.SectionID SectionID,
A.DocumentType DocumentType,
MIN(A.DocNumber) DocNumber,
MIN(A.SourceDocNumber) SourceDocNumber,
A.SortDocNumber SortDocNumber,
MIN(A.VATIdentNumber) VATIdentNumber,
MIN(A.VATDate$DATE) VATDate$DATE,
SUM(CASE WHEN VR.vatratetype = 1 THEN A.BaseAmount ELSE 0 END) zakl_dane1,
SUM(CASE WHEN VR.vatratetype = 2 THEN A.BaseAmount ELSE 0 END) zakl_dane2,
SUM(CASE WHEN VR.vatratetype = 3 THEN A.BaseAmount ELSE 0 END) zakl_dane3,
SUM(CASE WHEN VR.vatratetype = 1 THEN A.VATAmount ELSE 0 END) dan1,
SUM(CASE WHEN VR.vatratetype = 2 THEN A.VATAmount ELSE 0 END) dan2,
SUM(CASE WHEN VR.vatratetype = 3 THEN A.VATAmount ELSE 0 END) dan3,
MIN(A.Firm_ID) Firm_ID,
MIN(A.VATRate_ID) VATRate_ID
FROM
 VATClosings4 A
 JOIN Firms F ON F.ID=A.Firm_ID
 JOIN VATRates VR ON VR.ID=A.VATRate_ID

					
WHERE
 A.Parent_ID = :ID and A.SectionID='A2'
GROUP BY
 A.SectionID, A.DocumentType, A.SortDocNumber
ORDER BY
A.SectionID, A.DocumentType, A.SortDocNumber

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	CXUCAWC355R4FBLTTWMTLCPL0S
F	4K3EXM5PQBCL35CH000ILPWJF4	F	Firma
VR	ZSHD5MXH4Z24J0H2Y2R2VUZPEG	VR	DPH sazba

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
dan1	dtFloat	15			Daň 1
dan2	dtFloat	15			Daň 2
dan3	dtFloat	15			Daň 3
zakl_dane1	dtFloat	15			Základ daně 1
zakl_dane2	dtFloat	15			Základ daně 2
zakl_dane3	dtFloat	15			Základ daně 3

RowA3

Popis:

Kontrolní výkaz DPH A3

SQL definice:

SELECT
MIN(A.ID) ID,
A.SectionID SectionID,
A.DocumentType DocumentType,
MIN(A.DocNumber) DocNumber,
MIN(A.SourceDocNumber) SourceDocNumber,
A.SortDocNumber SortDocNumber,
MIN(A.VATIdentNumber) VATIdentNumber,
MIN(A.VATDate$DATE) VATDate$DATE,
SUM(A.BaseAmount) osv_plneni,
MIN(A.Firm_ID) Firm_ID,
MIN(A.VATRate_ID) VATRate_ID,
  (SELECT DateOfBirth$DATE FROM Persons P 
  WHERE P.ID = 
    CASE
      WHEN A.DocumentType = '01' THEN
        (SELECT AP.Person_ID FROM OtherIncomes AP WHERE AP.ID = A.Document_ID)
      WHEN A.DocumentType = '02' THEN
        (SELECT AP.Person_ID FROM OtherExpenses AP WHERE AP.ID = A.Document_ID)
      WHEN A.DocumentType = '03' THEN
        (SELECT AP.Person_ID FROM IssuedInvoices AP WHERE AP.ID = A.Document_ID)
      WHEN A.DocumentType = '04' THEN
        (SELECT AP.Person_ID FROM ReceivedInvoices AP WHERE AP.ID = A.Document_ID)
      WHEN A.DocumentType = '05' THEN
        (SELECT AP.Person_ID FROM CashReceived AP WHERE AP.ID = A.Document_ID)
      WHEN A.DocumentType = '06' THEN
        (SELECT AP.Person_ID FROM CashPaid AP WHERE AP.ID = A.Document_ID)
      WHEN A.DocumentType = '07' THEN
        (SELECT AP.Person_ID FROM RefundedCashReceived AP WHERE AP.ID = A.Document_ID)
      WHEN A.DocumentType = '08' THEN
        (SELECT AP.Person_ID FROM RefundedCashPaid AP WHERE AP.ID = A.Document_ID)
      WHEN A.DocumentType = '60' THEN
        (SELECT AP.Person_ID FROM IssuedCreditNotes AP WHERE AP.ID = A.Document_ID)
      WHEN A.DocumentType = '61' THEN
        (SELECT AP.Person_ID FROM ReceivedCreditNotes AP WHERE AP.ID = A.Document_ID)
      WHEN A.DocumentType = '62' THEN
        (SELECT AP.Person_ID FROM VATIssuedDInvoices AP WHERE AP.ID = A.Document_ID)
      WHEN A.DocumentType = '63' THEN
        (SELECT AP.Person_ID FROM VATIssuedDCreditNotes AP WHERE AP.ID = A.Document_ID)
      WHEN A.DocumentType = '64' THEN
        (SELECT AP.Person_ID FROM VATReceivedDInvoices AP WHERE AP.ID = A.Document_ID)
      WHEN A.DocumentType = '65' THEN
        (SELECT AP.Person_ID FROM VATReceivedDCreditNotes AP WHERE AP.ID = A.Document_ID)
      WHEN A.DocumentType = 'PD' THEN
        ' '
    ELSE
      ' '
    END
  ) AS Person_ID_DateOfBirth
FROM
 VATClosings4 A
 JOIN Firms F ON F.ID=A.Firm_ID
 JOIN VATRates VR ON VR.ID=A.VATRate_ID

					
WHERE
 A.Parent_ID = :ID and A.SectionID='A3'
GROUP BY
 A.SectionID, A.DocumentType, A.SortDocNumber, A.Document_ID
ORDER BY
 A.SectionID, A.DocumentType, A.SortDocNumber, A.Document_ID

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	CXUCAWC355R4FBLTTWMTLCPL0S
F	4K3EXM5PQBCL35CH000ILPWJF4	F	Firma
VR	ZSHD5MXH4Z24J0H2Y2R2VUZPEG	VR	DPH sazba

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
osv_plneni	dtFloat	15			Hodnota osvoboz. plnění
Person_ID_DateOfBirth	dtString	10			Datum narození osoby zadané na dokladu

RowA4

Popis:

Kontrolní výkaz DPH A4

SQL definice:

SELECT
MIN(A.ID) ID,
A.SectionID SectionID,
A.DocumentType DocumentType,
MIN(A.DocNumber) DocNumber,
MIN(A.SourceDocNumber) SourceDocNumber,
A.SortDocNumber SortDocNumber,
MIN(A.VATIdentNumber) VATIdentNumber,
MIN(A.VATDate$DATE) VATDate$DATE,
SUM(CASE WHEN VR.vatratetype = 1 THEN A.BaseAmount ELSE 0 END) zakl_dane1,
SUM(CASE WHEN VR.vatratetype = 2 THEN A.BaseAmount ELSE 0 END) zakl_dane2,
SUM(CASE WHEN VR.vatratetype = 3 THEN A.BaseAmount ELSE 0 END) zakl_dane3,
SUM(CASE WHEN VR.vatratetype = 1 THEN A.VATAmount ELSE 0 END) dan1,
SUM(CASE WHEN VR.vatratetype = 2 THEN A.VATAmount ELSE 0 END) dan2,
SUM(CASE WHEN VR.vatratetype = 3 THEN A.VATAmount ELSE 0 END) dan3,
MIN(A.Firm_ID) Firm_ID,
MIN(A.VATRate_ID) VATRate_ID,
CASE
  WHEN ib_find_token(UPPER(A.VATReportMode), 'ZVLR1') > 0 THEN '1'
  WHEN ib_find_token(UPPER(A.VATReportMode), 'ZVLR2') > 0 THEN '2'
  ELSE '0'
END KeyZVLR,
CASE
  WHEN ib_find_token(UPPER(A.VATReportMode), 'INS44') > 0 THEN '1'
  WHEN ib_find_token(UPPER(A.VATReportMode), 'ONP46') > 0 THEN '2'
  ELSE '0'
END KeyONP46
FROM
 VATClosings4 A
 JOIN Firms F ON F.ID=A.Firm_ID
 JOIN VATRates VR ON VR.ID=A.VATRate_ID

					
WHERE
 A.Parent_ID = :ID and A.SectionID='A4'
GROUP BY
 A.SectionID, A.DocumentType, A.SortDocNumber,
CASE
  WHEN ib_find_token(UPPER(A.VATReportMode), 'ZVLR1') > 0 THEN '1'
  WHEN ib_find_token(UPPER(A.VATReportMode), 'ZVLR2') > 0 THEN '2'
  ELSE '0'
END,
CASE
  WHEN ib_find_token(UPPER(A.VATReportMode), 'INS44') > 0 THEN '1'
  WHEN ib_find_token(UPPER(A.VATReportMode), 'ONP46') > 0 THEN '2'
  ELSE '0'
END
ORDER BY
A.SectionID, A.DocumentType, A.SortDocNumber,
CASE
  WHEN ib_find_token(UPPER(A.VATReportMode), 'ZVLR1') > 0 THEN '1'
  WHEN ib_find_token(UPPER(A.VATReportMode), 'ZVLR2') > 0 THEN '2'
  ELSE '0'
END,
CASE
  WHEN ib_find_token(UPPER(A.VATReportMode), 'INS44') > 0 THEN '1'
  WHEN ib_find_token(UPPER(A.VATReportMode), 'ONP46') > 0 THEN '2'
  ELSE '0'
END

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	CXUCAWC355R4FBLTTWMTLCPL0S
F	4K3EXM5PQBCL35CH000ILPWJF4	F	Firma
VR	ZSHD5MXH4Z24J0H2Y2R2VUZPEG	VR	DPH sazba

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
dan1	dtFloat	15			Daň 1
dan2	dtFloat	15			Daň 2
dan3	dtFloat	15			Daň 3
KeyONP46	dtString	1			Kód oprav nedobyt. pohl.
KeyZVLR	dtString	1			Kód zvl. režimu
zakl_dane1	dtFloat	15			Základ daně 1
zakl_dane2	dtFloat	15			Základ daně 2
zakl_dane3	dtFloat	15			Základ daně 3

RowA5

Popis:

Kontrolní výkaz DPH A5

SQL definice:

SELECT
MIN(A.ID) ID,
SUM(CASE WHEN VR.vatratetype = 1 THEN A.BaseAmount ELSE 0 END) zakl_dane1,
SUM(CASE WHEN VR.vatratetype = 2 THEN A.BaseAmount ELSE 0 END) zakl_dane2,
SUM(CASE WHEN VR.vatratetype = 3 THEN A.BaseAmount ELSE 0 END) zakl_dane3,
SUM(CASE WHEN VR.vatratetype = 1 THEN A.VATAmount ELSE 0 END) dan1,
SUM(CASE WHEN VR.vatratetype = 2 THEN A.VATAmount ELSE 0 END) dan2,
SUM(CASE WHEN VR.vatratetype = 3 THEN A.VATAmount ELSE 0 END) dan3
FROM
 VATClosings4 A
 JOIN VATRates VR ON VR.ID=A.VATRate_ID

					
WHERE
 A.Parent_ID = :ID and A.SectionID='A5'
GROUP BY
 A.SectionID
ORDER BY
 A.SectionID

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	CXUCAWC355R4FBLTTWMTLCPL0S
VR	ZSHD5MXH4Z24J0H2Y2R2VUZPEG	VR	DPH sazba

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
dan1	dtFloat	15			Daň 1
dan2	dtFloat	15			Daň 2
dan3	dtFloat	15			Daň 3
zakl_dane1	dtFloat	15			Základ daně 1
zakl_dane2	dtFloat	15			Základ daně 2
zakl_dane3	dtFloat	15			Základ daně 3

RowB1

Popis:

Kontrolní výkaz DPH B1

SQL definice:

SELECT
MIN(A.ID) ID,
A.SectionID SectionID,
A.DocumentType DocumentType,
MIN(A.DocNumber) DocNumber,
MIN(A.SourceDocNumber) SourceDocNumber,
A.SortDocNumber SortDocNumber,
MIN(A.VATIdentNumber) VATIdentNumber,
MIN(A.VATDate$DATE) VATDate$DATE,
SUM(CASE WHEN VR.vatratetype = 1 THEN A.BaseAmount ELSE 0 END) zakl_dane1,
SUM(CASE WHEN VR.vatratetype = 2 THEN A.BaseAmount ELSE 0 END) zakl_dane2,
SUM(CASE WHEN VR.vatratetype = 3 THEN A.BaseAmount ELSE 0 END) zakl_dane3,
SUM(CASE WHEN VR.vatratetype = 1 THEN A.VATAmount ELSE 0 END) dan1,
SUM(CASE WHEN VR.vatratetype = 2 THEN A.VATAmount ELSE 0 END) dan2,
SUM(CASE WHEN VR.vatratetype = 3 THEN A.VATAmount ELSE 0 END) dan3,
MIN(A.Firm_ID) Firm_ID,
MIN(A.VATRate_ID) VATRate_ID,
A.DRCCode DRCCode
FROM
 VATClosings4 A
 JOIN Firms F ON F.ID=A.Firm_ID
 JOIN VATRates VR ON VR.ID=A.VATRate_ID

					
WHERE
 A.Parent_ID = :ID and A.SectionID='B1'
GROUP BY
 A.SectionID, A.DocumentType, A.SortDocNumber, A.DRCCode
ORDER BY
 A.SectionID, A.DocumentType, A.SortDocNumber, A.DRCCode

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	CXUCAWC355R4FBLTTWMTLCPL0S
F	4K3EXM5PQBCL35CH000ILPWJF4	F	Firma
VR	ZSHD5MXH4Z24J0H2Y2R2VUZPEG	VR	DPH sazba

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
dan1	dtFloat	15			Daň 1
dan2	dtFloat	15			Daň 2
dan3	dtFloat	15			Daň 3
zakl_dane1	dtFloat	15			Základ daně 1
zakl_dane2	dtFloat	15			Základ daně 2
zakl_dane3	dtFloat	15			Základ daně 3

RowB2

Popis:

Kontrolní výkaz DPH B2

SQL definice:

SELECT
MIN(A.ID) ID,
A.SectionID SectionID,
A.DocumentType DocumentType,
MIN(A.DocNumber) DocNumber,
MIN(A.SourceDocNumber) SourceDocNumber,
A.SortDocNumber SortDocNumber,
MIN(A.VATIdentNumber) VATIdentNumber,
MIN(A.VATDate$DATE) VATDate$DATE,
SUM(CASE WHEN VR.vatratetype = 1 THEN A.BaseAmount ELSE 0 END) zakl_dane1,
SUM(CASE WHEN VR.vatratetype = 2 THEN A.BaseAmount ELSE 0 END) zakl_dane2,
SUM(CASE WHEN VR.vatratetype = 3 THEN A.BaseAmount ELSE 0 END) zakl_dane3,
SUM(CASE WHEN VR.vatratetype = 1 THEN A.VATAmount ELSE 0 END) dan1,
SUM(CASE WHEN VR.vatratetype = 2 THEN A.VATAmount ELSE 0 END) dan2,
SUM(CASE WHEN VR.vatratetype = 3 THEN A.VATAmount ELSE 0 END) dan3,
MIN(A.Firm_ID) Firm_ID,
MIN(A.VATRate_ID) VATRate_ID,
CASE
  WHEN ib_find_token(UPPER(A.VATReportMode), 'RATIO') > 0 THEN 'A'
  ELSE 'N'
END pomer,
CASE
  WHEN ib_find_token(UPPER(A.VATReportMode), 'INS44') > 0 THEN '1'
  WHEN ib_find_token(UPPER(A.VATReportMode), 'ONP46') > 0 THEN '2'
  ELSE '0'
END KeyONP46
FROM
 VATClosings4 A
 JOIN Firms F ON F.ID=A.Firm_ID
 JOIN VATRates VR ON VR.ID=A.VATRate_ID

					
WHERE
 A.Parent_ID = :ID and A.SectionID='B2'
GROUP BY
 A.SectionID, A.DocumentType, A.SortDocNumber,
CASE
  WHEN ib_find_token(UPPER(A.VATReportMode), 'RATIO') > 0 THEN 'A'
  ELSE 'N'
END,
CASE
  WHEN ib_find_token(UPPER(A.VATReportMode), 'INS44') > 0 THEN '1'
  WHEN ib_find_token(UPPER(A.VATReportMode), 'ONP46') > 0 THEN '2'
  ELSE '0'
END
ORDER BY
A.SectionID, A.DocumentType, A.SortDocNumber,
CASE
  WHEN ib_find_token(UPPER(A.VATReportMode), 'RATIO') > 0 THEN 'A'
  ELSE 'N'
END,
CASE
  WHEN ib_find_token(UPPER(A.VATReportMode), 'INS44') > 0 THEN '1'
  WHEN ib_find_token(UPPER(A.VATReportMode), 'ONP46') > 0 THEN '2'
  ELSE '0'
END

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	CXUCAWC355R4FBLTTWMTLCPL0S
F	4K3EXM5PQBCL35CH000ILPWJF4	F	Firma
VR	ZSHD5MXH4Z24J0H2Y2R2VUZPEG	VR	DPH sazba

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
dan1	dtFloat	15			Daň 1
dan2	dtFloat	15			Daň 2
dan3	dtFloat	15			Daň 3
KeyONP46	dtString	1			Kód oprav nedobyt. pohl.
pomer	dtString	1			Poměr
zakl_dane1	dtFloat	15			Základ daně 1
zakl_dane2	dtFloat	15			Základ daně 2
zakl_dane3	dtFloat	15			Základ daně 3

RowB3

Popis:

Kontrolní výkaz DPH B3

SQL definice:

SELECT
MIN(A.ID) ID,
SUM(CASE WHEN VR.vatratetype = 1 THEN A.BaseAmount ELSE 0 END) zakl_dane1,
SUM(CASE WHEN VR.vatratetype = 2 THEN A.BaseAmount ELSE 0 END) zakl_dane2,
SUM(CASE WHEN VR.vatratetype = 3 THEN A.BaseAmount ELSE 0 END) zakl_dane3,
SUM(CASE WHEN VR.vatratetype = 1 THEN A.VATAmount ELSE 0 END) dan1,
SUM(CASE WHEN VR.vatratetype = 2 THEN A.VATAmount ELSE 0 END) dan2,
SUM(CASE WHEN VR.vatratetype = 3 THEN A.VATAmount ELSE 0 END) dan3
FROM
 VATClosings4 A
 JOIN VATRates VR ON VR.ID=A.VATRate_ID
WHERE
 A.Parent_ID = :ID and A.SectionID='B3'
GROUP BY
 A.SectionID
ORDER BY
 A.SectionID

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	CXUCAWC355R4FBLTTWMTLCPL0S
VR	ZSHD5MXH4Z24J0H2Y2R2VUZPEG	VR	DPH sazba

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
dan1	dtFloat	15			Daň 1
dan2	dtFloat	15			Daň 2
dan3	dtFloat	15			Daň 3
zakl_dane1	dtFloat	15			Základ daně 1
zakl_dane2	dtFloat	15			Základ daně 2
zakl_dane3	dtFloat	15			Základ daně 3

Podmínky

ID

Šablona:

ID

Dataset:

MAIN

Speciální parametry:

CLASSID=EPXJ3GDLIOZ41FPIS3POB3NUCS

Generated by ABRA Software a.s. 27.10.2021 16:36