Dynamický dotaz - INTRASTAT - Export - Doklady s nevyplněnými údaji pro hlášení INTRASTAT

ID:

STFMHCEYLA0OL2DRGODOUB2RZ0

Datasety

MAIN

Popis:

INTRASTAT - Export - Doklady s nevyplněnými údaji pro hlášení INTRASTAT

SQL definice:

SELECT DISTINCT
  P.ID as ID,
  '03' as DocumentType,
  P.IntrastatCompleteKind as IntrastatCompleteKind
FROM
  IssuedInvoices2 A
  JOIN IssuedInvoices P ON P.ID=A.Parent_ID
  LEFT JOIN IntrastatExchangeRates IEX on IEX.Currency_ID = P.currency_id
  JOIN Firms F on F.ID=P.Firm_ID
WHERE
  A.RowType=3 and A.ToIntrastat = 'A' and P.TradeType in (2, 4, 5, 6, 7, 8) and
  (P.VATDate$Date >= {$DATE_FROM} and P.VATDate$Date < {$DATE_TO}) and P.VATDocument = 'A'
  and P.IntrastatCompleteKind in ({$IntrastatCompleteKindOnOutput})
{ANDWHERE}
union all
SELECT DISTINCT
  P.ID as ID,
  '05' as DocumentType,
  P.IntrastatCompleteKind as IntrastatCompleteKind
FROM
  CashReceived2 A
  JOIN CashReceived P ON P.ID=A.Parent_ID
  JOIN Firms F on F.ID=P.Firm_ID
WHERE
  A.RowType=3 and A.ToIntrastat = 'A' and P.TradeType in (2, 4, 5, 6, 7, 8) and
  (P.VATDate$Date >= {$DATE_FROM} and P.VATDate$Date < {$DATE_TO}) and P.VATDocument = 'A'
  and P.IntrastatCompleteKind in ({$IntrastatCompleteKindOnOutput})
{ANDWHERE}
union all
SELECT DISTINCT
  P.ID as ID,
  '60' as DocumentType,
  P.IntrastatCompleteKind as IntrastatCompleteKind
FROM
  IssuedCreditNotes2 A
  JOIN IssuedCreditNotes P ON P.ID=A.Parent_ID
  JOIN Firms F on F.ID=P.Firm_ID
WHERE
  A.RowType=3 and A.ToIntrastat = 'A' and P.TradeType in (2, 4, 5, 6, 7, 8) and
  (P.VATDate$Date >= {$DATE_FROM} and P.VATDate$Date < {$DATE_TO}) and P.VATDocument = 'A'
  and P.IntrastatCompleteKind in ({$IntrastatCompleteKindOnInput})
{ANDWHERE}
union all
SELECT DISTINCT
  P.ID as ID,
  '07' as DocumentType,
  P.IntrastatCompleteKind as IntrastatCompleteKind
FROM
  RefundedCashReceived2 A
  JOIN RefundedCashReceived P ON P.ID=A.Parent_ID
  JOIN Firms F on F.ID=P.Firm_ID
WHERE
  A.RowType=3 and A.ToIntrastat = 'A' and P.TradeType in (2, 4, 5, 6, 7, 8) and
  (P.VATDate$Date >= {$DATE_FROM} and P.VATDate$Date < {$DATE_TO}) and P.VATDocument = 'A'
  and P.IntrastatCompleteKind in ({$IntrastatCompleteKindOnInput})
{ANDWHERE}
union all
SELECT DISTINCT
  P.ID as ID,
  '20' as DocumentType,
  P.IntrastatCompleteKind as IntrastatCompleteKind
FROM
  StoreDocuments2 A
  JOIN PMStates PMS ON A.PMState_ID=PMS.ID
  JOIN StoreDocuments P ON P.ID=A.Parent_ID
  JOIN Firms F on F.ID=P.Firm_ID
WHERE
  PMS.SystemState=3 AND 
  A.RowType=3 and A.ToIntrastat = 'A' and P.TradeType in (2, 4, 5, 6) and P.DocumentType = '20' and
  (P.IntrastatDate$Date >= {$DATE_FROM} and P.IntrastatDate$Date < {$DATE_TO})
  and P.IntrastatCompleteKind in ({$IntrastatCompleteKindOnInput})
{ANDWHERE}
union all
SELECT DISTINCT
  P.ID as ID,
  '30' as DocumentType,
  P.IntrastatCompleteKind as IntrastatCompleteKind
FROM
  StoreDocuments2 A
  JOIN StoreDocuments P ON P.ID=A.Parent_ID
  JOIN Firms F on F.ID=P.Firm_ID
WHERE
  A.RowType=3 and A.ToIntrastat = 'A' and P.TradeType in (2, 4, 5, 6) and P.DocumentType = '30' and
  (P.IntrastatDate$Date >= {$DATE_FROM} and P.IntrastatDate$Date < {$DATE_TO})
  and P.IntrastatCompleteKind in ({$IntrastatCompleteKindOnOutput})
{ANDWHERE}
group by P.ID, P.IntrastatCompleteKind

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
DocumentType	dtString	2			Typ dokladu
ID	dtString	10			Vlastní ID dokladu
IntrastatCompleteKind	dtInteger	2			Stav vyplnění

Podmínky

Date

Popiska:

Datum

Typ/Subtyp:

ctDateTime/0

ckRange:

P.DocDate$DATE >= {:LOW} and
P.DocDate$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

Currency_ID

Šablona:

Currency_ID

Dataset:

MAIN

Country_ID

Šablona:

Country_ID - Target

Dataset:

MAIN

Store_ID

Šablona:

Store_ID

Dataset:

MAIN

StoreCard_ID

Šablona:

StoreCard_ID

Dataset:

MAIN

SmallArticles

Šablona:

SmallArticles

Dataset:

MAIN

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