Cockpit Beas Queries

Installation Instructions > Cockpit Dashboards & Kpi's for HANA > Cockpit Beas Queries

These are the Be.as Queries needed for the system.

You can configure these queries following the instructions on the Cockpit and Dashboards main help page: Cockpit Configuring Queries

The Queries needed are the following:

- Beas_BlockedQCOrders:

SELECT * from "BEAS_QSFTHAUPT"

- Beas_ConstructionOrdersWithResidue:

SELECT
T0."BELNR_ID" as "Fertigungsauftrag-Nr",
T0."BELPOS_ID" as "Fertigungsauftrag-Pos",
T0."ItemCode" as "Artikelnummer",
T0."ItemName" as "Artikelbezeichnung",
T0."MENGE_VERBRAUCH" as "Menge",
T0."GEL_MENGE" as "Gelieferte Menge",
T0."LIEFERDATUM" as "Bestagtigter Liefertmein"
from "BEAS_FTPOS" T0 inner join "BEAS_FTHAUPT" T1
on T0."BELNR_ID"=T1."BELNR_ID"
where T0."ABGKZ"='N' and T0."LIEFERDATUM" < NOW()

- Beas_CurrentStoppedResources:

SELECT * from "BEAS_APLATZ_STILLSTAND" where "DATUM_BIS">NOW()

- Beas_EmployeeWorkTime:

SELECT *, "ZEIT"/60 as seit_hour FROM "BEAS_ARBZEIT"

- Beas_OpenComplaints:

SELECT * from "BEAS_RECLAMATION" where "Status" = 1 and "TYP" = 1

- Beas_OpenedProductionOrders:

SELECT T0."BELNR_ID" as Fertigungsautfragsnummer,
T0."BELPOS_ID" as Fertigungsauftragsposition,
T0."ItemCode" as Baugruppennummer,
T0."ItemName" as Baugruppenname,
T0."MENGE_VERBRAUCH" as Menge,
T0."GEL_MENGE" as gMenge,
T0."ANFZEIT" as Startdatum,
T0."ENDZEIT" as Enddatum
From "BEAS_FTPOS" T0

- Beas_ProductionOrderStatus:

SELECT "AUFTRAGINT",
CAST("AUFTRAGINT" as VARCHAR) as "KA-Nr",
"MIN_SOLL",
"MIN_IST",
case when "MIN_IST"=0 then 0
when "MIN_SOLL"=0 then 0
when "MIN_SOLL" IS NULL then 0
when "MIN_IST" IS NULL then 0
else ("MIN_IST"/"MIN_SOLL"*100)
end as "Fertigungszeit in %",  
"MENGE",
"GEL_MENGE",
case when "GEL_MENGE"=0 then 0
when "MENGE"=0 then 0
when "GEL_MENGE" IS NULL then 0
when "MENGE" is null then 0
else "GEL_MENGE"/"MENGE"*100
end as "Materialverbrauch in %"
FROM "BEAS_FTPOS"
where CAST("AUFTRAGINT" as Varchar) >0

- Beas_ResourceStatus:

SELECT   "BEAS_APLATZ"."APLATZ_ID",  
        "BEAS_APLATZ"."BEZ",  
        "BEAS_APLATZ"."BITMAP_ID",  
        "BEAS_APLATZ"."LAYERPLAN",  
        "BEAS_APLATZ"."OPTIMIZED_ID",  
        "BEAS_APLATZ"."COLOR_ID",   "BEAS_APLATZ"."Active",
        (select max('('||rtrim("BEAS_PERS"."PERS_ID")||') '||rtrim("BEAS_PERS"."NAME1")||' '||rtrim("BEAS_PERS"."NAME2")) from "BEAS_FTSTMP","BEAS_PERS" where "BEAS_FTSTMP"."APLATZ_ID"="BEAS_APLATZ"."APLATZ_ID" and "BEAS_PERS"."PERS_ID"="BEAS_FTSTMP"."PERS_ID") as "Name",  
        (select min("BEAS_FTSTMP"."AB") from "BEAS_FTSTMP" where "BEAS_FTSTMP"."APLATZ_ID"="BEAS_APLATZ"."APLATZ_ID") as "AB",
          (select sum("ZEIT")  from "BEAS_RESOURCEN"  where ifnull("BEAS_RESOURCEN"."TYP",'')='' and "BEAS_RESOURCEN"."RESOURCE"="BEAS_APLATZ"."APLATZ_ID" and "VON" <= ADD_DAYS(now(),1 /*day*/)) / 60 as istkapa,
          (select sum(rc."SOLLKAPSTDTAG" * rc."ANZMASCHPERS") from "BEAS_RESOURCE_CALENDAR" rc where rc."RESOURCE" = "BEAS_APLATZ"."APLATZ_ID" and rc."DATE_ID" between ADD_DAYS(now(),-1) and  ADD_DAYS(now(),1 /*day*/)) as sollkapa,                
          (select max("GRUNDID") from "BEAS_APLATZ_STILLSTAND" where "APLATZ_ID"="BEAS_APLATZ"."APLATZ_ID" and "DATUM_VON" <= now() and "DATUM_BIS" >= now()) as stillstand
   FROM  "BEAS_APLATZ"
     where ifnull("BEAS_APLATZ"."GRUPPENPLATZ",'N')='N'  
ORDER BY "BEAS_APLATZ"."OPTIMIZED_SORT" ASC,  
        "BEAS_APLATZ"."APLATZ_ID" ASC

- Beas_Workorders:

SELECT * FROM "BEAS_FTPOS"