Dynamický dotaz - INTRASTAT - Přijetí

ID:

0012IW1H1BH4X24ZFUJKXGA5US

Datasety

MAIN

Popis:

INTRASTAT - Přijetí

SQL definice:

SELECT
  '20' as DocumentType,
  IE.Code as Extra,
  Sum(A.IntrastatAmount) as Amount,
  Sum(A.LocalIntrastatAmount) as LocalAmount,
  Sum(A.StatisticAmount) as StatisticAmount,
  P.Currency_ID as Currency_ID,
  CT.Code as CountryCode,
  IR.Code as IntrastatRegionCode,
  IC.Code as CommodityCode,
  IDT.Code as DeliveryTermCode,
  ITT.Code as TransportationTypeCode,
  IT.Code as TransactionTypeCode,
  Max(IC.UnitCode) as IntrastatUnitCode,
  Sum(CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6))) as IntrastatQuantity,
  Sum(CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) * CAST(S.IntrastatWeight AS DOUBLE PRECISION)) as Weight,
  S.IntrastatWeightUnit as WeightUnit,
  OCT.Code as OriginCountryCode,
  Max(IC.WeightIsOptional) as WeightIsOptional,
  Max(IC.ConstantWeight) as ConstantWeight,
  Max(IC.Description) as CommodityDescription
FROM
  StoreDocuments2 A
  JOIN PMStates PMS ON A.PMState_ID=PMS.ID
  JOIN StoreDocuments P ON P.ID=A.Parent_ID
  LEFT JOIN StoreCards S ON S.ID=A.StoreCard_ID
  JOIN Currencies C on C.ID=P.Currency_ID
  LEFT JOIN Countries OCT on OCT.ID=A.OriginCountry_ID
  JOIN Countries CT on CT.ID=P.Country_ID
  JOIN Firms F on F.ID=P.Firm_ID
  LEFT JOIN IntrastatRegions IR on IR.ID=A.IntrastatRegion_ID
  LEFT JOIN IntrastatCommodities IC on IC.ID=S.IntrastatCommodity_ID
  LEFT JOIN IntrastatExtraTypes IE on IE.ID=S.IntrastatExtraType_ID
  LEFT JOIN IntrastatDeliveryTerms IDT on IDT.ID=P.IntrastatDeliveryTerm_ID
  LEFT JOIN IntrastatTransportationTypes ITT on ITT.ID=P.IntrastatTransportationType_ID
  LEFT JOIN IntrastatTransactionTypes IT on IT.ID=P.IntrastatTransactionType_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}))
{ANDWHERE}
Group By
  P.Currency_ID,
  CT.Code,
  IR.Code,
  IC.Code,
  IDT.Code,
  ITT.Code,
  IT.Code,
  IE.Code,
  S.IntrastatWeightUnit,
  OCT.Code
union all
SELECT
  '60' as DocumentType,
  IE.Code as Extra,
  Sum(A.IntrastatAmount) as Amount,
  Sum(A.LocalIntrastatAmount) as LocalAmount,
  Sum(A.StatisticAmount) as StatisticAmount,
  P.Currency_ID as Currency_ID,
  CT.Code as CountryCode,
  IR.Code as IntrastatRegionCode,
  IC.Code as CommodityCode,
  IDT.Code as DeliveryTermCode,
  ITT.Code as TransportationTypeCode,
  IT.Code as TransactionTypeCode,
  Max(IC.UnitCode) as IntrastatUnitCode,
  Sum(CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6))) as IntrastatQuantity,
  Sum(CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) * CAST(S.IntrastatWeight AS DOUBLE PRECISION)) as Weight,
  S.IntrastatWeightUnit as WeightUnit,
  OCT.Code as OriginCountryCode,
  Max(IC.WeightIsOptional) as WeightIsOptional,
  Max(IC.ConstantWeight) as ConstantWeight,
  Max(IC.Description) as CommodityDescription
FROM
  IssuedCreditNotes2 A
  JOIN IssuedCreditNotes P ON P.ID=A.Parent_ID
  LEFT JOIN StoreCards S ON S.ID=A.StoreCard_ID
  JOIN Currencies C on C.ID=P.Currency_ID
  LEFT JOIN Countries OCT on OCT.ID=S.Country_ID
  JOIN Countries CT on CT.ID=P.Country_ID
  JOIN Firms F on F.ID=P.Firm_ID
  LEFT JOIN IntrastatRegions IR on IR.ID=A.IntrastatRegion_ID
  LEFT JOIN IntrastatCommodities IC on IC.ID=S.IntrastatCommodity_ID
  LEFT JOIN IntrastatExtraTypes IE on IE.ID=S.IntrastatExtraType_ID
  LEFT JOIN IntrastatDeliveryTerms IDT on IDT.ID=P.IntrastatDeliveryTerm_ID
  LEFT JOIN IntrastatTransportationTypes ITT on ITT.ID=P.IntrastatTransportationType_ID
  LEFT JOIN IntrastatTransactionTypes IT on IT.ID=P.IntrastatTransactionType_ID
WHERE
  A.RowType=3 and A.ToIntrastat = 'A' and P.TradeType in (2, 4, 5, 6, 7, 8) and P.VATDocument = 'A'
  AND
  ((P.VATDate$DATE >= {$DATE_FROM}) AND (P.VATDate$DATE < {$DATE_TO}))
{ANDWHERE}
Group By
  P.Currency_ID,
  CT.Code,
  IR.Code,
  IC.Code,
  IDT.Code,
  ITT.Code,
  IT.Code,
  IE.Code,
  S.IntrastatWeightUnit,
  OCT.Code
union all
SELECT
  '07' as DocumentType,
  IE.Code as Extra,
  Sum(A.IntrastatAmount) as Amount,
  Sum(A.LocalIntrastatAmount) as LocalAmount,
  Sum(A.StatisticAmount) as StatisticAmount,
  P.Currency_ID as Currency_ID,
  CT.Code as CountryCode,
  IR.Code as IntrastatRegionCode,
  IC.Code as CommodityCode,
  IDT.Code as DeliveryTermCode,
  ITT.Code as TransportationTypeCode,
  IT.Code as TransactionTypeCode,
  Max(IC.UnitCode) as IntrastatUnitCode,
  Sum(CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6))) as IntrastatQuantity,
  Sum(CAST(CAST(A.Quantity AS DOUBLE PRECISION) / CAST((CAST(S.IntrastatUnitRate AS DOUBLE PRECISION) / CAST(S.IntrastatUnitRateRef AS DOUBLE PRECISION)) AS DOUBLE PRECISION) as NUMERIC(15,6)) * CAST(S.IntrastatWeight AS DOUBLE PRECISION)) as Weight,
  S.IntrastatWeightUnit as WeightUnit,
  OCT.Code as OriginCountryCode,
  Max(IC.WeightIsOptional) as WeightIsOptional,
  Max(IC.ConstantWeight) as ConstantWeight,
  Max(IC.Description) as CommodityDescription
FROM
  RefundedCashReceived2 A
  JOIN RefundedCashReceived P ON P.ID=A.Parent_ID
  LEFT JOIN StoreCards S ON S.ID=A.StoreCard_ID
  JOIN Currencies C on C.ID=P.Currency_ID
  LEFT JOIN Countries OCT on OCT.ID=S.Country_ID
  JOIN Countries CT on CT.ID=P.Country_ID
  JOIN Firms F on F.ID=P.Firm_ID
  LEFT JOIN IntrastatRegions IR on IR.ID=A.IntrastatRegion_ID
  LEFT JOIN IntrastatCommodities IC on IC.ID=S.IntrastatCommodity_ID
  LEFT JOIN IntrastatExtraTypes IE on IE.ID=S.IntrastatExtraType_ID
  LEFT JOIN IntrastatDeliveryTerms IDT on IDT.ID=P.IntrastatDeliveryTerm_ID
  LEFT JOIN IntrastatTransportationTypes ITT on ITT.ID=P.IntrastatTransportationType_ID
  LEFT JOIN IntrastatTransactionTypes IT on IT.ID=P.IntrastatTransactionType_ID
WHERE
  A.RowType=3 and A.ToIntrastat = 'A' and P.TradeType in (2, 4, 5, 6, 7, 8) and P.VATDocument = 'A'
  AND
  ((P.VATDate$DATE >= {$DATE_FROM}) AND (P.VATDate$DATE < {$DATE_TO}))
{ANDWHERE}
GROUP BY
  P.Currency_ID,
  CT.Code,
  IR.Code,
  IC.Code,
  IDT.Code,
  ITT.Code,
  IT.Code,
  IE.Code,
  S.IntrastatWeightUnit,
  OCT.Code
ORDER BY
  7, 8, 9, 10, 11, 12, 2, 16, 17

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
Amount	dtFloat	10			Částka
CommodityCode	dtString	10			Kód komodity
CommodityDescription	dtString	240			Popis komodity
ConstantWeight	dtFloat	15			Konstantní hmotnost
CountryCode	dtString	3			Kód země
Currency_ID	dtString	10		4R5FINKNYNDL3C5P00CA141B44	Měna
DeliveryTermCode	dtString	3			Kód dodací podmínky
DocumentType	dtString	2			Typ dokladu
Extra	dtString	2			Zvláštní pohyb
IntrastatQuantity	dtFloat	10			Množství INTRASTAT
IntrastatRegionCode	dtString	10			Kód kraje
IntrastatUnitCode	dtString	3			Jednotka INTRASTAT
LocalAmount	dtFloat	10			Částka v lokální měně
OriginCountryCode	dtString	3			Kód země původu
StatisticAmount	dtFloat	10			Statistická hodnota
TransactionTypeCode	dtString	2			Kód povahy transakce
TransportationTypeCode	dtString	1			Kód druhu dopravy
Weight	dtFloat	10			Hmotnost
WeightIsOptional	dtBoolean	1			Nepovinná hmotnost
WeightUnit	dtInteger	10			Hmotnostní jednotka

Podmínky

Date

Popiska:

Datum

Typ/Subtyp:

ctDateTime/0

ckRange:

{:LOW}{:HIGH}

ckSingle:

<field> = {:VALUE}

ckList:

<field> IN ({:LIST})

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 - Source

Dataset:

MAIN

Store_ID

Šablona:

Store_ID

Dataset:

MAIN

StoreCard_ID

Šablona:

StoreCard_ID

Dataset:

MAIN

SmallArticles

Šablona:

SmallArticles

Dataset:

MAIN

CumulateTransactions

Šablona:

CumulateTransactions

Dataset:

MAIN

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