Ziel:
Mit dieser SQL Anweisung werden die Informationen eines Objektes abgefragt (Anzahl der Arbeiten, Bescheide, Vermerke etc.)
DECLARE @OB_ID AS VARCHAR(36)
SET @OB_ID = :OB_ID
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(rs.RS_ID)
FROM NET_RECHT_SACHEN rs with(nolock)
WHERE rs.OB_ID = @OB_ID),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 101 Rechtsachen
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(ver.VER_ID)
FROM NET_VERMERK ver with(nolock)
WHERE ver.OB_ID = @OB_ID),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 102 Vermerke
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(ve.VE_ID)
FROM NET_VERTRAG ve with(nolock)
LEFT OUTER JOIN NET_OBJEKT ob with(nolock)
ON(ob.OB_ID = ve.OB_ID)
LEFT OUTER JOIN NET_PER_EINHEIT_ZU pei with(nolock)
ON(pei.PEI_ID = ve.PEI_ID)
LEFT OUTER JOIN NET_EINHEIT eh with(nolock)
ON(pei.EH_ID = eh.EH_ID)
WHERE (ve.OB_ID = @OB_ID
OR ve.VE_ID IN (SELECT VE_ID FROM NET_VERTRAG_OBJEKT_ZU with(nolock) WHERE OB_ID = @OB_ID))
OR eh.OB_ID = @OB_ID),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 103 Verträge
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(be.SV_ID)
FROM NET_BELASTUNGEN be with(nolock)
WHERE be.OB_ID = @OB_ID),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 104 Belastungen
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(auf.AUF_ID)
FROM NET_AUFTRAG2 auf with(nolock)
WHERE auf.OB_ID = @OB_ID),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 105 Aufträge
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(ha.HA_ID)
FROM NET_HAFTUNG ha with(nolock)
WHERE ha.OB_ID = @OB_ID),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 106 Haftung
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(ar.AR_ID)
FROM NET_ARBEIT ar with(nolock)
WHERE ar.OB_ID = @OB_ID),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 107 Arbeiten
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(sc.SC_ID)
FROM NET_SCHADEN sc with(nolock)
WHERE sc.OB_ID = @OB_ID),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 108 Schäden
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(ma.MA_ID)
FROM NET_MANGEL ma with(nolock)
WHERE ma.OB_ID = @OB_ID),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 130 MÄNGEL
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(inv.INV_ID)
FROM NET_INVERTAR inv with(nolock)
WHERE inv.OB_ID = @OB_ID
OR(inv.INV_ID IN (SELECT INV_ID FROM NET_INVENTAR_OBJEKT_ZU with(nolock) WHERE OB_ID = @OB_ID))),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 120 Inventar
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(bt.BT_ID)
FROM NET_BESCHEID_TERMIN bt with(nolock)
WHERE bt.OB_ID = @OB_ID
AND bt.BT_KATEGORIE = 'Bescheid'),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 112 Bescheid
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(bt.BT_ID)
FROM NET_BESCHEID_TERMIN bt with(nolock)
WHERE bt.OB_ID = @OB_ID
AND bt.BT_KATEGORIE = 'Termin'),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 113 Termin
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(az.AZ_ID)
FROM NET_AUSSTATTUNG_ZU az with(nolock)
WHERE az.OB_ID = @OB_ID
AND az.EH_ID is null),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 140 Ausstattung
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(ppl.OB_ID)
FROM NET_PARKPLATZ ppl with(nolock)
WHERE ppl.OB_ID = @OB_ID),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 150 Parkplätze
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(itd.ITD_ID)
FROM NET_INTERESSENT_DETAIL itd with(nolock)
WHERE itd.OB_ID = @OB_ID),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 160 Interessenten
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(wgz.GFZ_ID)
FROM NET_WF_GF_ZU wgz with(nolock)
WHERE wgz.GFZ_FREMD_ID = @OB_ID),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 170 Geschäftsfall
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(obb.OBB_ID)
FROM NET_OBJEKT_BUDGET obb with(nolock)
WHERE obb.OB_ID = @OB_ID
AND obb.OBB_TYP = 'OBJEKTBUDGET'),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 180 Objektbudget
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(au.OB_ID)
FROM NET_AUSSCHREIBUNG au with(nolock)
WHERE au.OB_ID = @OB_ID
AND au.AUS_GBB = 1),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 190 Ausschreibung Bau
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(au.OB_ID)
FROM NET_AUSSCHREIBUNG au with(nolock)
WHERE au.OB_ID = @OB_ID
AND au.AUS_GBH = 1),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 195 Ausschreibung HV
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(ma.OB_ID)
FROM NET_MANGEL ma with(nolock)
WHERE ma.OB_ID = @OB_ID),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 200 Gewährleistung
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(ma.OB_ID)
FROM NET_MASSENMANGEL ma with(nolock)
WHERE ma.OB_ID = @OB_ID
AND ma.MM_IST_GBB = 1
),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 210 Schlussabnahme Bau
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(ma.OB_ID)
FROM NET_MASSENMANGEL ma with(nolock)
WHERE ma.OB_ID = @OB_ID
AND MM_IST_GBH = 1
),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 220 Schlussabnahme HV
AND COD.COD_KEY3 = 1
UNION ALL
SELECT
COD.COD_KEY1 AS ART,
COD.COD_KEY2 AS SORTIERUNG,
COD.COD_TEXT1 AS NAME,
'ANZAHL' = (SELECT
count(obb.OBB_ID)
FROM NET_OBJEKT_BUDGET obb with(nolock)
WHERE obb.OB_ID = @OB_ID
AND obb.OBB_TYP = 'BAUBUDGET'),
0 AS BUTTON_NEW,
0 AS BUTTON_LISTE
FROM CP_CONFIG CON with(nolock)
INNER JOIN CP_CONFIG_DETAIL COD with(nolock)
ON(CON.CON_ID = COD.CON_ID)
WHERE CON.CON_NUMBER = 1530
AND COD.COD_KEY1 = 230 --Baukonto
AND COD.COD_KEY3 = 1
ORDER BY SORTIERUNG