Dynamický dotaz - DPH - Evidence režimu Přenesení daňové povinnosti na výstupu

ID:

N5BMZMGRMY4OVJWVHPXECK5NE0

Datasety

MAIN

Popis:

DPH - Evidence režimu Přenesení daňové povinnosti na výstupu

SQL definice:

SELECT
  DQ.DocumentType AS DocumentType,
  P.ID AS DocID,
  MAX(P.Firm_ID) AS Firm_ID,
  MAX(F.VATIdentNumber) as VATIdentNumber,
  MAX(P.VATDate$DATE) AS VATDate$DATE,
  SUM(A.LocalTAmountWithoutVAT) as LocalAmount,
  SUM(A.TAmountWithoutVAT) as Amount,
  MAX(DQ.Code || '-' || CAST(P.OrdNumber AS VARCHAR(10)) || '/' || PE.Code) AS DocNumber,
  DA.Code as DRCCode,
  SUM(A.DRCQuantity) as DRCQuantity,
  MAX(DA.Qunit) AS DRCUnit,
  MAX(DA.Name) as DRCName,
  MAX(DA.ID) as DRC_ID,
  MAX(0) AS AcknowledgeDate$DATE
FROM
  IssuedInvoices2 A
  JOIN IssuedInvoices P ON P.ID=A.Parent_ID
  JOIN Firms F on F.ID = P.Firm_ID
  JOIN Periods PE ON PE.ID=P.Period_ID
  JOIN DocQueues DQ ON DQ.ID=P.DocQueue_ID
  LEFT JOIN DRCArticles DA ON DA.ID=A.DRCArticle_ID
WHERE
  (A.RowType in (1,2,3,4,5)) 
  and (P.TradeType = 1) and (P.IsReverseChargeDeclared='A')
  and (A.VATMode = 1)
  and (P.VATDate$Date >= {$DATE_FROM} and P.VATDate$Date < {$DATE_TO})
  and P.VATDocument = 'A'
{ANDWHERE}
GROUP BY DQ.DocumentType, P.ID, DA.Code
union all
SELECT
  DQ.DocumentType AS DocumentType,
  P.ID AS DocID,
  MAX(P.Firm_ID) AS Firm_ID,
  MAX(F.VATIdentNumber) as VATIdentNumber,
  MAX(P.VATDate$DATE) AS VATDate$DATE,
  SUM(A.LocalTAmountWithoutVAT) as LocalAmount,
  SUM(A.TAmountWithoutVAT) as Amount,
  MAX(DQ.Code || '-' || CAST(P.OrdNumber AS VARCHAR(10)) || '/' || PE.Code) AS DocNumber,
  DA.Code as DRCCode,
  SUM(A.DRCQuantity) as DRCQuantity,
  MAX(DA.Qunit) AS DRCUnit,
  MAX(DA.Name) as DRCName,
  MAX(DA.ID) as DRC_ID,
  MAX(0) AS AcknowledgeDate$DATE
FROM
  CashReceived2 A
  JOIN CashReceived P ON P.ID=A.Parent_ID
  JOIN Firms F on F.ID = P.Firm_ID
  JOIN Periods PE ON PE.ID=P.Period_ID
  JOIN DocQueues DQ ON DQ.ID=P.DocQueue_ID
  LEFT JOIN DRCArticles DA ON DA.ID=A.DRCArticle_ID
WHERE
  (A.RowType in (1,2,3,4,5))
  and (P.TradeType = 1) and (P.IsReverseChargeDeclared='A')
  and (A.VATMode = 1)
  and (P.VATDate$Date >= {$DATE_FROM} and P.VATDate$Date < {$DATE_TO})
  and P.VATDocument = 'A'
{ANDWHERE}
GROUP BY DQ.DocumentType, P.ID, DA.Code
union all
SELECT
  DQ.DocumentType AS DocumentType,
  P.ID AS DocID,
  MAX(P.Firm_ID) AS Firm_ID,
  MAX(F.VATIdentNumber) as VATIdentNumber,
  MAX(P.VATDate$DATE) AS VATDate$DATE,
  SUM(-1*A.LocalTAmountWithoutVAT) as LocalAmount,
  SUM(-1*A.TAmountWithoutVAT) as Amount,
  MAX(DQ.Code || '-' || CAST(P.OrdNumber AS VARCHAR(10)) || '/' || PE.Code) AS DocNumber,
  DA.Code as DRCCode,
  SUM(-1*A.DRCQuantity) as DRCQuantity,
  MAX(DA.Qunit) AS DRCUnit,
  MAX(DA.Name) as DRCName,
  MAX(DA.ID) as DRC_ID,
  MAX(COALESCE(CNA.DocDate$DATE,0,CNA.DocDate$DATE)) AS AcknowledgeDate$DATE  
FROM
  IssuedCreditNotes2 A
  JOIN IssuedCreditNotes P ON P.ID=A.Parent_ID
  JOIN Firms F on F.ID = P.Firm_ID
  JOIN Periods PE ON PE.ID=P.Period_ID
  JOIN DocQueues DQ ON DQ.ID=P.DocQueue_ID
  LEFT JOIN DRCArticles DA ON DA.ID=A.DRCArticle_ID
  LEFT join CreditNotesAcknowledges CNA ON CNA.CreditNote_ID = P.ID
WHERE
  (A.RowType in (1,2,3,4,5))
  and (P.TradeType = 1) and (P.IsReverseChargeDeclared='A')
  and (A.VATMode = 1)
  and ( (P.Acknowledge = 0 and (P.VATDate$Date >= {$DATE_FROM} and P.VATDate$Date < {$DATE_TO})) or
  (P.Acknowledge = 2 and (CNA.DocDate$Date >= {$DATE_FROM} and CNA.DocDate$Date < {$DATE_TO})) )
  and P.VATDocument = 'A'
{ANDWHERE}
GROUP BY DQ.DocumentType, P.ID, DA.Code
union all
SELECT
  DQ.DocumentType AS DocumentType,
  P.ID AS DocID,
  MAX(P.Firm_ID) AS Firm_ID,
  MAX(F.VATIdentNumber) as VATIdentNumber,
  MAX(P.VATDate$DATE) AS VATDate$DATE,
  SUM(-1*A.LocalTAmountWithoutVAT) as LocalAmount,
  SUM(-1*A.TAmountWithoutVAT) as Amount,
  MAX(DQ.Code || '-' || CAST(P.OrdNumber AS VARCHAR(10)) || '/' || PE.Code) AS DocNumber,
  DA.Code as DRCCode,
  SUM(-1*A.DRCQuantity) as DRCQuantity,
  MAX(DA.Qunit) AS DRCUnit,
  MAX(DA.Name) as DRCName,
  MAX(DA.ID) as DRC_ID,
  MAX(0) AS AcknowledgeDate$DATE
FROM
  RefundedCashReceived2 A
  JOIN RefundedCashReceived P ON P.ID=A.Parent_ID
  JOIN Firms F on F.ID = P.Firm_ID
  JOIN Periods PE ON PE.ID=P.Period_ID
  JOIN DocQueues DQ ON DQ.ID=P.DocQueue_ID
  LEFT JOIN DRCArticles DA ON DA.ID=A.DRCArticle_ID
WHERE
  (A.RowType in (1,2,3,4,5))
  and (P.TradeType = 1) and (P.IsReverseChargeDeclared='A')
  and (A.VATMode = 1)
  and (P.VATDate$Date >= {$DATE_FROM} and P.VATDate$Date < {$DATE_TO})
  and P.VATDocument = 'A'
{ANDWHERE}
GROUP BY DQ.DocumentType, P.ID, DA.Code
ORDER BY 4,5,1,2

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
AcknowledgeDate$DATE	dtDateTime	10			Datum potvrzení dobropisu
Amount	dtFloat	15			Částka
DocID	dtString	10			ID dokladu
DocNumber	dtString	16			Číslo dokladu
DocumentType	dtString	2			Typ dokladu
DRC_ID	dtString	10		GPNLRNS45JO4X0JEYKW0P0KEB4	Typ plnění
DRCCode	dtString	20			Kód typu plnění
DRCName	dtString	150			Název plnění
DRCQuantity	dtFloat	15			Vykazované množství
DRCUnit	dtString	20			Vykazovaná jednotka
Firm_ID	dtString	10		4K3EXM5PQBCL35CH000ILPWJF4	Firma
LocalAmount	dtFloat	15			Částka lokální
VATDate$DATE	dtDateTime	10			Datum plnění
VATIdentNumber	dtString	15			DIČ

IssuedInvoices

Popis:

Faktury vydané

SQL definice:

SELECT {FIELDS} 
FROM
  IssuedInvoices A 
WHERE
  (:DocumentType='03')
  and A.ID=:DocID

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	O3BDOKTWEFD13ACM03KIU0CLP4

CashReceived

Popis:

Pokladna příjem

SQL definice:

SELECT {FIELDS} 
FROM
  CashReceived A 
WHERE
  (:DocumentType='05')
  and A.ID=:DocID

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	WG02MSU3BBDL3ACR03KIU0CLP4

IssuedCreditNotes

Popis:

Dobropisy faktur vydaných

SQL definice:

SELECT {FIELDS} 
FROM
  IssuedCreditNotes A 
WHERE
  (:DocumentType='60')
  and A.ID=:DocID

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	W402MSU3BBDL3ACR03KIU0CLP4

RefundedCashReceived

Popis:

Vrácení pokladního příjmu

SQL definice:

SELECT {FIELDS} 
FROM
  RefundedCashReceived A 
WHERE
  (:DocumentType='07')
  and A.ID=:DocID

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	WW02MSU3BBDL3ACR03KIU0CLP4

Podmínky

VATDate

Popiska:

Datum

Typ/Subtyp:

ctDateTime/0

ckRange:

P.VATDate$DATE >= {:LOW} and
P.VATDate$DATE < {:HIGH}

Dataset:

MAIN

Speciální parametry:

AlwaysUsed=True
ReplacementLink=True
{$DATE_FROM}=VALUE;0
{$DATE_TO}=VALUEHIGH;0

Division_ID

Šablona:

Division_ID

Dataset:

MAIN

Firm_ID

Šablona:

Firm_ID (joined)

Dataset:

MAIN

BusOrder_ID

Šablona:

BusOrder_ID

Dataset:

MAIN

BusTransaction_ID

Šablona:

BusTransaction_ID

Dataset:

MAIN

BusProject_ID

Šablona:

BusProject_ID

Dataset:

MAIN

CreatedBy_ID

Šablona:

CreatedBy_ID

Dataset:

MAIN

CorrectedBy_ID

Šablona:

CorrectedBy_ID

Dataset:

MAIN

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