Dynamický dotaz - DPH - Export dokladů v režimu MOSS do SAF-MOSS

ID:

XIJQXNNLGPYOFA1RT2HMSWGBN0

Datasety

MAIN

Popis:

DPH - Export dokladů v režimu MOSS do SAF-MOSS

SQL definice:

SELECT
  A.ID,
  DQ.DocumentType,
  CAST(DQ.Code || '-' || IB_LPad(CAST(A.OrdNumber AS VARCHAR(6)), 6, '0') || '/' || PE.Code AS VARCHAR(20)) SortDocNumber,
  '0000000000' AS Source_ID,
  A.VATDate$DATE AS MOSSVATDate$DATE
  {ANDFIELDS}
FROM
  IssuedInvoices A
  JOIN Firms F on F.ID = A.Firm_ID
  JOIN Periods PE ON PE.ID=A.Period_ID
  JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
  JOIN Countries CV ON CV.ID=A.VATCountry_ID
  JOIN Countries CC ON CC.ID=A.Country_ID
  JOIN Currencies CR ON CR.ID=A.Currency_ID
WHERE
  (A.TradeType = 7) and
  (A.VATDate$Date >= {$DATE_FROM} and A.VATDate$Date < {$DATE_TO}) and
  (A.VATDocument = 'A')
{ANDWHERE}
UNION ALL
SELECT
  A.ID,
  DQ.DocumentType,
  CAST(DQ.Code || '-' || IB_LPad(CAST(A.OrdNumber AS VARCHAR(6)), 6, '0') || '/' || PE.Code AS VARCHAR(20)) SortDocNumber,
  A.Source_ID AS Source_ID,
  CASE {$WhatOfVATDate}
  WHEN 1 THEN
    A.MOSSVATDate$Date
  ELSE
    CASE A.Acknowledge
      WHEN 2 THEN CNA.DocDate$Date
      ELSE A.VATDate$DATE
    END
  END AS MOSSVATDate$DATE
  {ANDFIELDS}
FROM
  IssuedCreditNotes A
  LEFT join CreditNotesAcknowledges CNA ON CNA.CreditNote_ID = A.ID
  JOIN Firms F on F.ID = A.Firm_ID
  JOIN Periods PE ON PE.ID=A.Period_ID
  JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
  JOIN Countries CV ON CV.ID=A.VATCountry_ID
  JOIN Countries CC ON CC.ID=A.Country_ID
  JOIN Currencies CR ON CR.ID=A.Currency_ID
WHERE
  (A.TradeType = 7) and
  (A.VATDocument = 'A') and
  /* Rozliseni data plneni */
  (
  ({$WhatOfVATDate} = 1 and
  ( (A.Acknowledge = 0 and (A.MOSSVATDate$Date >= {$DATE_FROM} and A.MOSSVATDate$Date < {$DATE_TO})) or
  (A.Acknowledge = 2 and (A.MOSSVATDate$Date >= {$DATE_FROM} and A.MOSSVATDate$Date < {$DATE_TO})) )
  )
  or
  ({$WhatOfVATDate} <> 1 and
  ( (A.Acknowledge = 0 and (A.VATDate$Date >= {$DATE_FROM} and A.VATDate$Date < {$DATE_TO})) or
  (A.Acknowledge = 2 and (CNA.DocDate$Date >= {$DATE_FROM} and CNA.DocDate$Date < {$DATE_TO})) )
  )
  )

					
{ANDWHERE}
UNION ALL
SELECT
  A.ID,
  DQ.DocumentType,
  CAST(DQ.Code || '-' || IB_LPad(CAST(A.OrdNumber AS VARCHAR(6)), 6, '0') || '/' || PE.Code AS VARCHAR(20)) SortDocNumber,
  '0000000000' AS Source_ID,
  A.VATDate$DATE AS MOSSVATDate$DATE
  {ANDFIELDS}
FROM
  CashReceived A
  JOIN Firms F on F.ID = A.Firm_ID
  JOIN Periods PE ON PE.ID=A.Period_ID
  JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
  JOIN Countries CV ON CV.ID=A.VATCountry_ID
  JOIN Countries CC ON CC.ID=A.Country_ID
  JOIN Currencies CR ON CR.ID=A.Currency_ID
WHERE
  (A.TradeType = 7) and
  (A.VATDate$Date >= {$DATE_FROM} and A.VATDate$Date < {$DATE_TO}) and
  (A.VATDocument = 'A')
{ANDWHERE}
UNION ALL
SELECT
  A.ID,
  DQ.DocumentType,
  CAST(DQ.Code || '-' || IB_LPad(CAST(A.OrdNumber AS VARCHAR(6)), 6, '0') || '/' || PE.Code AS VARCHAR(20)) SortDocNumber,
  A.Source_ID AS Source_ID,
  CASE {$WhatOfVATDate}
  WHEN 1 THEN
    A.MOSSVATDate$Date
  ELSE
    A.VATDate$DATE
  END AS MOSSVATDate$DATE
  {ANDFIELDS}
FROM
  RefundedCashReceived A
  JOIN Firms F on F.ID = A.Firm_ID
  JOIN Periods PE ON PE.ID=A.Period_ID
  JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
  JOIN Countries CV ON CV.ID=A.VATCountry_ID
  JOIN Countries CC ON CC.ID=A.Country_ID
  JOIN Currencies CR ON CR.ID=A.Currency_ID
WHERE
  (A.TradeType = 7) and
  (A.VATDocument = 'A') and
  /* Rozliseni data plneni */
  (
  ({$WhatOfVATDate} = 1 and
  ((A.MOSSVATDate$Date >= {$DATE_FROM} and A.MOSSVATDate$Date < {$DATE_TO}))
  )
  or
  ({$WhatOfVATDate} <> 1 and
  ((A.VATDate$Date >= {$DATE_FROM} and A.VATDate$Date < {$DATE_TO}))
  )
  )
{ANDWHERE}
UNION ALL
SELECT
  A.ID,
  DQ.DocumentType,
  CAST(DQ.Code || '-' || IB_LPad(CAST(A.OrdNumber AS VARCHAR(6)), 6, '0') || '/' || PE.Code AS VARCHAR(20)) SortDocNumber,
  '0000000000' AS Source_ID,
  A.VATDate$DATE AS MOSSVATDate$DATE
  {ANDFIELDS}
FROM
  VATIssuedDInvoices A
  JOIN Firms F on F.ID = A.Firm_ID
  JOIN Periods PE ON PE.ID=A.Period_ID
  JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
  JOIN Countries CV ON CV.ID=A.VATCountry_ID
  JOIN Countries CC ON CC.ID=A.Country_ID
  JOIN Currencies CR ON CR.ID=A.Currency_ID
WHERE
  (A.TradeType = 7) and
  (A.VATDate$Date >= {$DATE_FROM} and A.VATDate$Date < {$DATE_TO})
{ANDWHERE}
UNION ALL
SELECT
  A.ID,
  DQ.DocumentType,
  CAST(DQ.Code || '-' || IB_LPad(CAST(A.OrdNumber AS VARCHAR(6)), 6, '0') || '/' || PE.Code AS VARCHAR(20)) SortDocNumber,
  A.Source_ID AS Source_ID,
  CASE {$WhatOfVATDate}
  WHEN 1 THEN
    A.MOSSVATDate$Date
  ELSE
    A.VATDate$DATE
  END AS MOSSVATDate$DATE
  {ANDFIELDS}
FROM
  VATIssuedDCreditNotes A
  JOIN Firms F on F.ID = A.Firm_ID
  JOIN Periods PE ON PE.ID=A.Period_ID
  JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
  JOIN Countries CV ON CV.ID=A.VATCountry_ID
  JOIN Countries CC ON CC.ID=A.Country_ID
  JOIN Currencies CR ON CR.ID=A.Currency_ID
WHERE
  (A.TradeType = 7) and
  /* Rozliseni data plneni */
  (
  ({$WhatOfVATDate} = 1 and
  ((A.MOSSVATDate$Date >= {$DATE_FROM} and A.MOSSVATDate$Date < {$DATE_TO}))
  )
  or
  ({$WhatOfVATDate} <> 1 and
  ((A.VATDate$Date >= {$DATE_FROM} and A.VATDate$Date < {$DATE_TO}))
  )
  )
{ANDWHERE}
ORDER BY 2, 3

Aliasy:

Alias	CLSID objektu	Prefix	Popis
CC	4J5FINKNYNDL3C5P00CA141B44	Country	Země
CR	4R5FINKNYNDL3C5P00CA141B44	Currency	Měna
CV	4J5FINKNYNDL3C5P00CA141B44	VATCountry	Země
DQ	OFTMKVQH3ZD13ACL03KIU0CLP4	DocQueue	Řada dokladů
F	4K3EXM5PQBCL35CH000ILPWJF4	Firm	Firma
PE	CAQPAYUOXVCL3ACL03KIU0CLP4	Period	Období

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
Amount	dtFloat	15	A.Amount		Částka s daní
AmountWithoutVAT	dtFloat	15	A.AmountWithoutVAT		Částka bez daně
CorrectedAt$DATE	dtDateTime	10	A.CorrectedAt$DATE		Datum opravy
Country_ID	dtString	10	A.Country_ID	4J5FINKNYNDL3C5P00CA141B44	Země určení
CreatedAt$DATE	dtDateTime	10	A.CreatedAt$DATE		Datum vytvoření
Currency_ID	dtString	10	A.Currency_ID	4R5FINKNYNDL3C5P00CA141B44	Měna
DocDate$DATE	dtDateTime	10	A.DocDate$DATE		Datum dokladu
DocumentNumber	dtString	20	CAST(DQ.Code || '-' || CAST(A.OrdNumber AS VARCHAR(10)) || '/' || PE.Code AS VARCHAR(20))		Číslo dokladu
DocumentType	dtString	2			Typ dokladu
Firm_ID	dtString	10	A.Firm_ID	4K3EXM5PQBCL35CH000ILPWJF4	Firma
ID	dtString	10		05UWJSK233DL3CBW01K0LEYWLS	ID dokladu
MOSSVATDate$DATE	dtDateTime	10			Datum plnění oprav. dokladu
RoundingAmount	dtFloat	15	A.RoundingAmount		Částka zaokrouhlení
SortDocNumber	dtString	20			Číslo dokladu pro třídění
Source_ID	dtString	10		05UWJSK233DL3CBW01K0LEYWLS	Zdrojový doklad
VATCountry_ID	dtString	10	A.VATCountry_ID	4J5FINKNYNDL3C5P00CA141B44	Země přiznání
VATDate$DATE	dtDateTime	10	A.VATDate$DATE		Datum plnění

ROWS

Popis:

Řádky

SQL definice:

SELECT 
{FIELDS}
FROM
  SAFMOSSRows(:DocumentType, :ID)
  A
  LEFT JOIN MOSSServices MS ON MS.ID = A.MOSSService_ID
  LEFT JOIN StoreCards SC ON SC.ID = A.StoreCard_ID
  JOIN VATRates VR ON VR.ID = A.VATRate_ID
  JOIN VATIndexes VI ON VI.ID = A.VATIndex_ID  
{WHERE}   
ORDER BY %PosIndex%

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
BusOrder_ID	dtString	10	A.BusOrder_ID	K2WTYL304VD13ACL03KIU0CLP4	Zakázka
BusProject_ID	dtString	10	A.BusProject_ID	QOKMKIQUJF34L3DUICTBWEDQJC	Projekt
BusTransaction_ID	dtString	10	A.BusTransaction_ID	KAWTYL304VD13ACL03KIU0CLP4	Obch.případ
Division_ID	dtString	10	A.Division_ID	O1X54EUXPZCL35CH000ILPWJF4	Středisko
ID	dtString	10	A.ID		ID řádku dokladu
LocalTAmount	dtFloat	15	A.LocalTAmount		Částka s daní lokální
LocalTAmountWithoutVAT	dtFloat	15	A.LocalTAmountWithoutVAT		Částka bez daně lokální
MOSSService_ID	dtString	10	A.MOSSService_ID	T3A2BFUHCOHOVEQW04IM2GCRM4	ID MOSS
PosIndex	dtInteger	5	A.PosIndex		Pořadí
Quantity	dtFloat	15	A.Quantity		Množství
QUnit	dtString	5	A.QUnit		Jednotka
RowType	dtInteger	2	A.RowType		Typ řádku
StoreCard_ID	dtString	10	A.StoreCard_ID	C3V5QDVZ5BDL342M01C0CX3FCC	Skl.karta
TAmount	dtFloat	15	A.TAmount		Částka s daní
TAmountWithoutVAT	dtFloat	15	A.TAmountWithoutVAT		Částka bez daně
Text	dtString	100	A.Text		Popis
UnitRate	dtFloat	15	A.UnitRate		UnitRate
VATIndex_ID	dtString	10	A.VATIndex_ID	04OGVINDGZD13FY1010DELDFKK	DPH index
VATRate_ID	dtString	10	A.VATRate_ID	ZSHD5MXH4Z24J0H2Y2R2VUZPEG	ID DPH sazby

SourceDocuments

Popis:

Zdrojové doklady

SQL definice:

SELECT 
  DQ.DocumentType, 
  CAST(DQ.Code || '-' || CAST(A.OrdNumber AS VARCHAR(10)) || '/' || PE.Code AS VARCHAR(20)) AS DocumentNumber,
  {FIELDS}
FROM IssuedInvoices A
  JOIN Periods PE ON PE.ID = A.Period_ID
  JOIN DocQueues DQ ON DQ.ID = A.DocQueue_ID
WHERE 
  (:DocumentType = '60') AND (A.ID = :Source_ID)
UNION ALL
SELECT
  DQ.DocumentType, 
  CAST(DQ.Code || '-' || CAST(A.OrdNumber AS VARCHAR(10)) || '/' || PE.Code AS VARCHAR(20)) AS DocumentNumber,
  {FIELDS}
FROM CashReceived A
  JOIN Periods PE ON PE.ID = A.Period_ID
  JOIN DocQueues DQ ON DQ.ID = A.DocQueue_ID
WHERE 
  (:DocumentType = '07') AND (A.ID = :Source_ID)
UNION ALL
SELECT
  DQ.DocumentType, 
  CAST(DQ.Code || '-' || CAST(A.OrdNumber AS VARCHAR(10)) || '/' || PE.Code AS VARCHAR(20)) AS DocumentNumber,
  {FIELDS}
FROM VATIssuedDInvoices A
  JOIN Periods PE ON PE.ID = A.Period_ID
  JOIN DocQueues DQ ON DQ.ID = A.DocQueue_ID
WHERE 
  (:DocumentType = '63') AND (A.ID = :Source_ID)

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
DocumentNumber	dtString	20			Číslo dokladu
DocumentType	dtString	2			Typ dokladu
ID	dtString	10	A.ID		ID dokladu

CreditNotes

Popis:

Dobropisy

SQL definice:

SELECT
  DQ.DocumentType, 
  CAST(DQ.Code || '-' || CAST(A.OrdNumber AS VARCHAR(10)) || '/' || PE.Code AS VARCHAR(20)) AS DocumentNumber,
  {FIELDS}
FROM IssuedCreditNotes A
  JOIN Periods PE ON PE.ID = A.Period_ID
  JOIN DocQueues DQ ON DQ.ID = A.DocQueue_ID
WHERE 
  (:DocumentType = '03') AND (A.Source_ID = :ID)
UNION ALL
SELECT
  DQ.DocumentType, 
  CAST(DQ.Code || '-' || CAST(A.OrdNumber AS VARCHAR(10)) || '/' || PE.Code AS VARCHAR(20)) AS DocumentNumber,
  {FIELDS}
FROM RefundedCashReceived A
  JOIN Periods PE ON PE.ID = A.Period_ID
  JOIN DocQueues DQ ON DQ.ID = A.DocQueue_ID
WHERE 
  (:DocumentType = '05') AND (A.Source_ID = :ID)
UNION ALL
SELECT
  DQ.DocumentType, 
  CAST(DQ.Code || '-' || CAST(A.OrdNumber AS VARCHAR(10)) || '/' || PE.Code AS VARCHAR(20)) AS DocumentNumber,
  {FIELDS}
FROM VATIssuedDCreditNotes A
  JOIN Periods PE ON PE.ID = A.Period_ID
  JOIN DocQueues DQ ON DQ.ID = A.DocQueue_ID
WHERE 
  (:DocumentType = '62') AND (A.Source_ID = :ID)

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
DocumentNumber	dtString	20			Číslo dokladu
DocumentType	dtString	2			Typ dokladu
ID	dtString	10	A.ID		ID dokladu
ReasonDescription	dtString	160	A.ReasonDescription		Důvod opravy

Payments

Popis:

Platby

SQL definice:

SELECT {FIELDS} FROM
PAYMENTSFORDOCUMENT(:DocumentType, :ID, 0) B
INNER JOIN DOCQUEUES DQ ON DQ.ID = B.DOCQUEUE_ID
INNER JOIN PERIODS P ON P.ID = B.PERIOD_ID
INNER JOIN CURRENCIES C ON C.ID = B.CURRENCY_ID
LEFT JOIN BANKACCOUNTS BA ON BA.ID = B.BANKACCOUNT_ID
LEFT JOIN CASHDESKS CD ON CD.ID = B.CASHDESK_ID

Aliasy:

Alias	CLSID objektu	Prefix	Popis
BA	S1DAHWVT5ND133N4010DELDFKK	BankAccount	Bankovní účet
C	4R5FINKNYNDL3C5P00CA141B44	Currency	Měna
CD	SDDAHWVT5ND133N4010DELDFKK	CashDesk	Pokladna
DQ	OFTMKVQH3ZD13ACL03KIU0CLP4	DocQueue	Zdrojová řada
P	CAQPAYUOXVCL3ACL03KIU0CLP4	Period	Období

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
Amount	dtFloat	10	B.AMOUNT		Částka
DISPLAYNAME	dtString	30	CAST(DQ.CODE || '-' || CAST(B.OrdNumber AS VARCHAR(10)) || '/' || P.CODE AS VARCHAR(30))		Název dokladu
DOCDATE$DATE	dtDateTime	10	B.DOCDATE$DATE		Datum dokladu
LocalAmount	dtFloat	10	B.LOCALAMOUNT		Částka v lokální měně
LocalPaidAmount	dtFloat	10	B.LOCALPAMOUNT		Částka platby v lokální měně
PaidAmount	dtFloat	10	B.PAMOUNT		Částka platby

Podmínky

VATDate

Šablona:

VATDate

Dataset:

MAIN

Speciální parametry:

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

WhatOfVATDate

Popiska:

Opravné doklady zahrnout podle data plnění

Typ/Subtyp:

ctSpecial/1

ckSingle:

{:VALUE}

Dataset:

MAIN

Speciální parametry:

Names=Opravného dokladu;Původního dokladu
Values=0;1
ReplacementLink=True
{$WhatOfVATDate}=VALUE;0
Hidden=False
AlwaysUsed=True

Country_ID

Šablona:

Country_ID - Target

Dataset:

MAIN

VATCountry_ID

Šablona:

VATCountry_ID

Dataset:

MAIN

Firm_ID

Šablona:

Firm_ID (joined)

Dataset:

MAIN

DocQueue_ID

Šablona:

DocQueue_ID

Dataset:

MAIN

Speciální parametry:

FilterDocumentType=03,60,05,07,62,63

Period_ID

Šablona:

Period_ID

Dataset:

MAIN

OrdNumber

Šablona:

OrdNumber

Dataset:

MAIN

DocDate

Šablona:

DocDate

Dataset:

MAIN

Currency_ID

Šablona:

Currency_ID

Dataset:

MAIN

Amount

Šablona:

Amount

Dataset:

MAIN

Speciální parametry:

LocalAmount

Šablona:

LocalAmount

Dataset:

MAIN

Speciální parametry:

PaymentDate

Šablona:

PaymentDate

Dataset:

MAIN

CreatedBy_ID

Šablona:

CreatedBy_ID

Dataset:

MAIN

CorrectedBy_ID

Šablona:

CorrectedBy_ID

Dataset:

MAIN

CreatedAt

Šablona:

CreatedAt

Dataset:

MAIN

CorrectedAt

Šablona:

CorrectedAt

Dataset:

MAIN

RowsDivision_ID

Šablona:

Division_ID

Dataset:

ROWS

Speciální parametry:

Hidden=True

RowsBusOrder_ID

Šablona:

BusOrder_ID

Dataset:

ROWS

Speciální parametry:

Hidden=True

RowsBusTransaction_ID

Šablona:

BusTransaction_ID

Dataset:

ROWS

Speciální parametry:

Hidden=True

RowsBusProject_ID

Šablona:

BusProject_ID

Dataset:

ROWS

Speciální parametry:

Hidden=True

RowsMOSSService_ID

Popiska:

Druh poskytnuté služby MOSS

Typ/Subtyp:

ctSpecial/0

ckSingle:

A.MOSSService_ID = {:VALUE}

ckList:

A.MOSSService_ID IN ({:LIST})

Dataset:

ROWS

Speciální parametry:

CLASSID=FQCZU4ZFHVAOP3WKBD1EX5X32W
TextField=Code
Hidden=True

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