Dynamický dotaz - INTRASTAT - Export - Skladové karty s nevyplněnou kombinovanou nomenklaturou nebo hmotností doplňkové jednotky

ID:

243SWPDD3ZS4NDFFJFGX1COQJC

Datasety

MAIN

Popis:

INTRASTAT - Export - Skladové karty s nevyplněnou kombinovanou nomenklaturou, hmotností doplňkové jednotky nebo zvl. pohybem

SQL definice:

SELECT
  A.StoreCard_ID as ID,
  S.IntrastatCommodity_ID as IntrastatCommodity,
  S.IntrastatExtraType_ID as IntrastatExtraType  
FROM
  IssuedInvoices2 A
  LEFT JOIN StoreCards S ON S.ID=A.StoreCard_ID
  JOIN IssuedInvoices P ON P.ID=A.Parent_ID
  LEFT JOIN IntrastatCommodities C ON C.ID = S.IntrastatCommodity_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 
  ((S.IntrastatCommodity_ID is null)or
  ({$CHECKET}='A' and S.IntrastatExtraType_ID is null) or
    ((S.IntrastatCommodity_ID is not null)and((S.IntrastatWeight = 0)and(C.WeightIsOptional = 'N')and(C.ConstantWeight=0)))
  )
{ANDWHERE}
union
SELECT
  A.StoreCard_ID as ID,
  S.IntrastatCommodity_ID as IntrastatCommodity,
  S.IntrastatExtraType_ID as IntrastatExtraType  
FROM
  CashReceived2 A
  LEFT JOIN StoreCards S ON S.ID=A.StoreCard_ID
  JOIN CashReceived P ON P.ID=A.Parent_ID
  LEFT JOIN IntrastatCommodities C ON C.ID = S.IntrastatCommodity_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
  ((S.IntrastatCommodity_ID is null)or
  ({$CHECKET}='A' and S.IntrastatExtraType_ID is null) or
   ((S.IntrastatCommodity_ID is not null)and((S.IntrastatWeight = 0)and(C.WeightIsOptional = 'N')and(C.ConstantWeight=0)))
  )
{ANDWHERE}
union
SELECT
  A.StoreCard_ID as ID,
  S.IntrastatCommodity_ID as IntrastatCommodity,
  S.IntrastatExtraType_ID as IntrastatExtraType  
FROM
  IssuedCreditNotes2 A
  LEFT JOIN StoreCards S ON S.ID=A.StoreCard_ID
  JOIN IssuedCreditNotes P ON P.ID=A.Parent_ID
  LEFT JOIN IntrastatCommodities C ON C.ID = S.IntrastatCommodity_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
  ((S.IntrastatCommodity_ID is null)or
  ({$CHECKET}='A' and S.IntrastatExtraType_ID is null) or
   ((S.IntrastatCommodity_ID is not null)and((S.IntrastatWeight = 0)and(C.WeightIsOptional = 'N')and(C.ConstantWeight=0)))
  )
{ANDWHERE}
union
SELECT
  A.StoreCard_ID as ID,
  S.IntrastatCommodity_ID as IntrastatCommodity,
  S.IntrastatExtraType_ID as IntrastatExtraType  
FROM
  RefundedCashReceived2 A
  LEFT JOIN StoreCards S ON S.ID=A.StoreCard_ID
  JOIN RefundedCashReceived P ON P.ID=A.Parent_ID
  LEFT JOIN IntrastatCommodities C ON C.ID = S.IntrastatCommodity_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
  ((S.IntrastatCommodity_ID is null)or
  ({$CHECKET}='A' and S.IntrastatExtraType_ID is null) or
   ((S.IntrastatCommodity_ID is not null)and((S.IntrastatWeight = 0)and(C.WeightIsOptional = 'N')and(C.ConstantWeight=0)))
  )
{ANDWHERE}
union
SELECT
  A.StoreCard_ID as ID,
  S.IntrastatCommodity_ID as IntrastatCommodity,
  S.IntrastatExtraType_ID as IntrastatExtraType  
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
  LEFT JOIN IntrastatCommodities C ON C.ID = S.IntrastatCommodity_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 IN ('20', '30') and
  (P.IntrastatDate$Date >= {$DATE_FROM} and P.IntrastatDate$Date < {$DATE_TO}) and
  ((S.IntrastatCommodity_ID is null)or
  ({$CHECKET}='A' and S.IntrastatExtraType_ID is null) or
   ((S.IntrastatCommodity_ID is not null)and((S.IntrastatWeight = 0)and(C.WeightIsOptional = 'N')and(C.ConstantWeight=0)))
  )
{ANDWHERE}
group by A.StoreCard_ID, S.IntrastatCommodity_ID, S.IntrastatExtraType_ID 

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
ID	dtString	10		C3V5QDVZ5BDL342M01C0CX3FCC	Skladová karta
IntrastatCommodity	dtString	10		0DD0C5LW4R54N4LUDW2VJG33P4	Nomenklatura

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

CheckIntrastatExtraType

Šablona:

CheckIntrastatExtraType

Dataset:

MAIN

Speciální parametry:

DefaultComboIndex=1

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