Dynamický dotaz - CRM - Report Pohledávek dle NACE

ID:

OSLUUZH4DB4O3FV04WGX3AR01C

Datasety

MAIN

Popis:

Obor činnosti NACE

SQL definice:

SELECT {FIELDS},
(COALESCE(A.Code,A.Code,'      ') ||
 COALESCE(B.Code,B.Code,'      ') ||
 COALESCE(C.Code,C.Code,'      ') ||
 COALESCE(D.Code,D.Code,'      ') ||
 COALESCE(E.Code,E.Code,'      ')) AS CodeKey
FROM NACE A
  {JOIN}
WHERE (A.Parent_ID = '0000000000') OR (A.Parent_ID IS NULL)
  and (E.Hidden = 'N' or E.Hidden is null)
  and (D.Hidden = 'N' or D.Hidden is null) and (C.Hidden = 'N' or C.Hidden is null)
  and (B.Hidden = 'N' or B.Hidden is null) and (A.Hidden = 'N' or A.Hidden is null)
AND
(
  (({$NACESelID}
  = ''
  )
  OR
  (A.ID in
  (SELECT Bx.ID FROM NACE Bx WHERE Bx.Hidden = 'N' AND
    ( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})) OR
    (({$NACEWithChilds} in ('1', 'A')) and
    (Bx.ID in (select ID from Sys$NACE2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})))))
  )))
OR
  (({$NACESelID}
  = ''
  )
  OR
  (B.ID in
  (SELECT Bx.ID FROM NACE Bx WHERE Bx.Hidden = 'N' AND
    ( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})) OR
    (({$NACEWithChilds} in ('1', 'A')) and
    (Bx.ID in (select ID from Sys$NACE2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})))))
  )))
OR
  (({$NACESelID}
  = ''
  )
  OR
  (C.ID in
  (SELECT Bx.ID FROM NACE Bx WHERE Bx.Hidden = 'N' AND
    ( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})) OR
    (({$NACEWithChilds} in ('1', 'A')) and
    (Bx.ID in (select ID from Sys$NACE2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})))))
  )))
OR
  (({$NACESelID}
  = ''
  )
  OR
  (D.ID in
  (SELECT Bx.ID FROM NACE Bx WHERE Bx.Hidden = 'N' AND
    ( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})) OR
    (({$NACEWithChilds} in ('1', 'A')) and
    (Bx.ID in (select ID from Sys$NACE2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})))))
  )))
OR
  (({$NACESelID}
  = ''
  )
  OR
  (E.ID in
  (SELECT Bx.ID FROM NACE Bx WHERE Bx.Hidden = 'N' AND
    ( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})) OR
    (({$NACEWithChilds} in ('1', 'A')) and
    (Bx.ID in (select ID from Sys$NACE2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})))))
  )))
)
{ANDWHERE}
ORDER BY CodeKey

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	GEPU3UY3U2POB4VWN4FIPOAQEG	Level_1
B	GEPU3UY3U2POB4VWN4FIPOAQEG	Level_2	NACE kód
C	GEPU3UY3U2POB4VWN4FIPOAQEG	Level_3	NACE kód
D	GEPU3UY3U2POB4VWN4FIPOAQEG	Level_4	NACE kód
E	GEPU3UY3U2POB4VWN4FIPOAQEG	Level_5	NACE kód

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
L1Amount	dtFloat	15	(select TotalAmount from FirmReceivableAmount(A.ID, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$FirmSelID}, null) )		Tržba
L2Amount	dtFloat	10	(select TotalAmount from FirmReceivableAmount(B.ID, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$FirmSelID}, null) )		Tržba
L3Amount	dtFloat	10	(select TotalAmount from FirmReceivableAmount(C.ID, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$FirmSelID}, null) )		Tržba
L4Amount	dtFloat	10	(select TotalAmount from FirmReceivableAmount(D.ID, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$FirmSelID}, null) )		Tržba
L5Amount	dtFloat	10	(select TotalAmount from FirmReceivableAmount(E.ID, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$FirmSelID}, null) )		Tržba

Podmínky

ID

Šablona:

ID

Dataset:

MAIN

Speciální parametry:

CLASSID=GEPU3UY3U2POB4VWN4FIPOAQEG

Code

Šablona:

Code

Dataset:

MAIN

Speciální parametry:

Hidden=True

Name

Šablona:

Name

Dataset:

MAIN

Speciální parametry:

Hidden=True

NotZero

Popiska:

Jen nenulové hodnoty

Typ/Subtyp:

ctBoolean/0

ckSingle:

(
({:Value} = 'A')
OR
({:Value} = 'N')
)

Dataset:

MAIN

NACE_ID

Šablona:

NACE_ID (Replacement link)

Dataset:

MAIN

DocDate

Šablona:

DocDate

Dataset:

MAIN

Speciální parametry:

ReplacementLink=True
{$DocDateFrom}=VALUE;0
{$DocDateTo}=VALUEHIGH;0
{$DocDateUsed}=USED;'N'

DueDate

Šablona:

DueDate

Dataset:

MAIN

Speciální parametry:

ReplacementLink=True
{$DueDateFrom}=VALUE;0
{$DueDateTo}=VALUEHIGH;0
{$DueDateUsed}=USED;'N'

Firm_ID

Šablona:

Firm_ID (Replacement Link)

Dataset:

MAIN

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