| 136 | MyOrders | OrdersPage | 0 | | using System;public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | IF (@p1 > 0)
BEGIN
SELECT
dbo.T_Orders.subject AS Chemical,
dbo.T_Order_Responsibles.name AS responible_name,
dbo.T_Orders.login_guid,
dbo.T_Workflow_States.state_name AS workflow_state,
dbo.T_Orders.CreatedDate,
dbo.T_Orders.EditedDate,
dbo.T_Orders.DeadlineDate
FROM
dbo.T_Orders
INNER JOIN dbo.T_Order_Responsibles
ON dbo.T_Orders.t_company_id = @p1
AND dbo.T_Orders.t_responsible_id = dbo.T_Order_Responsibles.id
LEFT OUTER JOIN dbo.T_Companies
ON dbo.T_Orders.t_company_id = dbo.T_Companies.id
LEFT OUTER JOIN dbo.T_Workflow_State_Datas
ON dbo.T_Orders.t_workflow_state_data_id = dbo.T_Workflow_State_Datas.id
LEFT OUTER JOIN dbo.T_Workflow_States
ON dbo.T_Workflow_State_Datas.t_workflow_State_id = dbo.T_Workflow_States.id
END
ELSE
BEGIN
SELECT
dbo.T_Orders.subject AS Chemical,
dbo.T_Order_Responsibles.name AS responible_name,
dbo.T_Orders.login_guid,
dbo.T_Workflow_States.state_name AS workflow_state,
dbo.T_Orders.CreatedDate,
dbo.T_Orders.EditedDate,
dbo.T_Orders.DeadlineDate
FROM
dbo.T_Orders
INNER JOIN dbo.T_Order_Responsibles
ON dbo.T_Orders.t_company_id IN (SELECT t_company_id FROM dbo.T_Customer_Company WHERE t_customer_id = @p0)
AND dbo.T_Orders.t_responsible_id = dbo.T_Order_Responsibles.id
LEFT OUTER JOIN dbo.T_Companies
ON dbo.T_Orders.t_company_id = dbo.T_Companies.id
LEFT OUTER JOIN dbo.T_Workflow_State_Datas
ON dbo.T_Orders.t_workflow_state_data_id = dbo.T_Workflow_State_Datas.id
LEFT OUTER JOIN dbo.T_Workflow_States
ON dbo.T_Workflow_State_Datas.t_workflow_State_id = dbo.T_Workflow_States.id
END
| Report description.
| Delete | Edit |
| 192 | Chemicals not in use in any process apv on hierarchy | ReportsPage | 10 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
p.Produktnavn as Product,
p.ProduktID as ProductNumber
FROM ProduktTabel p
INNER JOIN APB_Brugssted_RM_TBL apbrm ON apbrm.ProduktID = p.ProduktID
INNER JOIN T_Hierarchys h ON apbrm.t_hierarchy_id = @p0 AND h.id = apbrm.t_hierarchy_id
WHERE
-- CHECK IF ANY PROCESS APV FOR THIS HIERARCHY EXISTS WITH THIS PRODUCT
not exists(
select
ap.ProduktID
from
T_APV_Process_Hierachies ph
inner join T_APV_Process_Produkt ap on ap.APVProcessID = ph.APVProcessID and ap.ProduktID = p.ProduktID
where
HierarchyID = h.id
) | All chemicals in a hierarchy that are not part of any process apv on that hierarchy.
| Delete | Edit |
| 223 | Chemical Report APV Requirement, one hierarchy element | ReportsPage | 10 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
p.Produktnavn AS Product,
('Instruktion') AS DocLink_noexport,
ISNULL(et.[R_phrases_CLP], '-') AS ProductClassification,
ISNULL(s.SupplierName, '') AS SupplierName,
a.Anvendelsesområde AS Usage,
CASE
WHEN sds.v30 IS NULL
OR sds.v30 LIKE 'Ikke anvendelig%'
OR sds.v30 LIKE '-'
OR sds.v30 LIKE 'Not applicable%'
OR sds.v30 LIKE 'EUH210'
OR sds.v30 LIKE '-¤'
OR sds.v30 LIKE '¤'
OR sds.v30 LIKE 'Ingen¤%'
THEN ''
ELSE REPLACE(REPLACE(sds.v30, 'Sikkerhedsdatablad kan på anmodning rekvireres. (EUH210)', ''), '¤', '')
END AS OtherRemark,
' ' + (select top 1 Item from GHSStringToImageList(et.piktogram_CLP,'24','background-color:white; padding:4px;border:solid 1px #c0c0c0;margin:1px;')) + ' ' as picto_noexport_imgsrc,
p.[Fysisk tilstand] as Tilstandsform,
(STUFF((SELECT ('' + 'SDS')
from T_Files where id in (select top 1 fileid from T_ProductFiles where ProductId = p.produktid and not Deprecated = 1 and US = 0 order by CreatedDate desc)
FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'),1, 0, N'')) as SDSLinks_noexport,
isnull(es.APV_ES_navn,'') as es,
p.[Dessin nr] as ProductNumber
FROM ProduktTabel AS p
INNER JOIN Anvendelsesområde_RM AS a ON a.AnvendelsesområdeID = p.AnvendelsesområdeID
LEFT JOIN RM_supplier AS rms ON p.produktid = rms.produktid
LEFT JOIN EtiketTBL et on et.ProduktID = p.ProduktID and sprog = 'DK'
LEFT JOIN suppliertbl AS s ON rms.supplier = s.supplierid
INNER JOIN APB_Brugssted_RM_TBL AS apbrm ON apbrm.ProduktID = p.ProduktID
INNER JOIN T_Hierarchys AS h ON apbrm.t_hierarchy_id = @p0 AND h.id = apbrm.t_hierarchy_id
LEFT JOIN SDSVersionTbl AS sds ON p.ProduktID = sds.v98 AND sds.Released = 1 AND sds.Type LIKE '3_CLP' AND V99 LIKE 'DK'
LEFT OUTER JOIN ProduktSubstansTabel ps ON p.ProduktID = ps.ProduktID
LEFT OUTER JOIN SubstansTabel sub ON ps.SubstansID = sub.SubstansID
left outer join APV_anvendelseTBL es on es.APV_anvendelse_ID = apbrm.AnvendelseES_ID
WHERE ISNULL(rms.Supplier, 0) = CASE
WHEN ISNULL(@p1, 0) > 0
THEN @p1
ELSE ISNULL(rms.Supplier, 0)
END
AND
(
(
p.klassificering like '%H300%'
OR p.klassificering like '%H301%'
OR p.klassificering like '%H310%'
OR p.klassificering like '%H311%'
OR p.klassificering like '%H330%'
OR p.klassificering like '%H331%'
OR p.klassificering like '%H370%'
OR p.klassificering like '%H350%'
OR p.klassificering like '%H360%'
OR p.klassificering like '%H340%'
OR p.klassificering like '%H317%'
OR p.klassificering like '%H334%'
)
OR
(
(
sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%'
)
AND
(
(((sub.[DK Kraeftlisten] = 1 and ps.Amount >= 0.1) OR p.klassificering LIKE '%CARC. 1%' OR p.klassificering like '%muta%'))
OR
(sub.[DK Kraeftlisten] <> 1 and p.klassificering LIKE '%CARC. 2%' and ps.Amount >= 1.0)
)
)
) | Lists all chemicals on a specific location. | Delete | Edit |
| 230 | Chemical Report, one hierarchy element | ReportsPage | 10 | AlphaOmegaSuperUsers;AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | declare @aoLang as varchar(2) = case lower(@p2) when 'da-dk' then 'dk' else 'en' end;
DECLARE @risktable TABLE
(produktid int PRIMARY KEY)
insert into @risktable
select distinct p.produktid from ProduktTabel p
inner join ProduktSubstansTabel ps on ps.ProduktID = p.ProduktID
inner join SubstansTabel sub on sub.SubstansID = ps.SubstansID
and (
(
p.klassificering like '%H300%'
OR p.klassificering like '%H301%'
OR p.klassificering like '%H310%'
OR p.klassificering like '%H311%'
OR p.klassificering like '%H330%'
OR p.klassificering like '%H331%'
OR p.klassificering like '%H370%'
OR p.klassificering like '%H350%'
OR p.klassificering like '%H360%'
OR p.klassificering like '%H340%'
OR p.klassificering like '%H317%'
OR p.klassificering like '%H334%'
)
OR
(
(
sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%'
)
AND
(
((sub.[DK Kraeftlisten] = 1 and ps.Amount >= 0.1) OR p.klassificering LIKE '%CARC. 1%' OR p.klassificering like '%muta%')
OR ((sub.[DK Kraeftlisten] = 1 and ps.Amount >= 1.0) OR p.klassificering LIKE '%CARC. 2%')
)
)
);
SELECT DISTINCT p.Produktnavn AS Product,
isnull(CONCAT(isnull(p.klassificering,''),' ', dbo.GetPatternMatches('%EUH[0-9][0-9][0-9]%',et.Andenmaerkning_CLP,6, ' ')),'-') AS ProductClassification,
ISNULL(s.SupplierName, '') AS SupplierName,
case lower(@p2)
when 'en-us' then a.AnvendelsesområdeEN
else a.Anvendelsesområde
end as Usage,
CASE
WHEN sds.v30 IS NULL
OR sds.v30 LIKE 'Ikke anvendelig%'
OR sds.v30 LIKE '-'
OR sds.v30 LIKE 'Not applicable%'
OR sds.v30 LIKE 'EUH210'
OR sds.v30 LIKE '-¤'
OR sds.v30 LIKE '¤'
OR sds.v30 LIKE 'Ingen¤%'
THEN ''
ELSE REPLACE(REPLACE(sds.v30, 'Sikkerhedsdatablad kan på anmodning rekvireres. (EUH210)', ''), '¤', '')
END AS OtherRemark,
' ' + (select top 1 Item from GHSStringToImageList(et.piktogram_CLP,'24','background-color:white; padding:4px;border:solid 1px #c0c0c0;margin:1px;')) + ' ' as picto_noexport_imgsrc,
et.piktogram_CLP as GHSNumbers,
p.[Fysisk tilstand] as Tilstandsform,
(STUFF((SELECT ('' + 'SDS')
from T_Files where id in (select top 1 fileid from T_ProductFiles where ProductId = p.produktid and not Deprecated = 1 and US = 0 order by CreatedDate desc)
FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'),1, 0, N'')) as SDSLinks_noexport,
apbrm.Departments,
p.[Dessin nr] as ProductNumber,
(STUFF((SELECT (', ' + sub.[CAS nr])
FROM ProduktSubstansTabel prs
inner join SubstansTabel sub on sub.SubstansID = prs.SubstansID
WHERE
prs.ProduktID = p.ProduktID
FOR XML PATH('')), 1, 2, '')) AS CAS_nr,
cast((select case when exists(select produktid from @risktable where produktid = p.ProduktID) then 1 else 0 end) as bit) as IsAtRisk
FROM ProduktTabel AS p
INNER JOIN Anvendelsesområde_RM AS a ON a.AnvendelsesområdeID = p.AnvendelsesområdeID
LEFT JOIN RM_supplier AS rms ON p.produktid = rms.produktid
LEFT JOIN EtiketTBL et on et.ProduktID = p.ProduktID and lower(sprog) = @aoLang
LEFT JOIN suppliertbl AS s ON rms.supplier = s.supplierid
INNER JOIN APB_Brugssted_RM_TBL AS apbrm ON apbrm.ProduktID = p.ProduktID
INNER JOIN T_Hierarchys AS h ON apbrm.t_hierarchy_id = @p0
AND h.id = apbrm.t_hierarchy_id
LEFT JOIN SDSVersionTbl AS sds ON p.ProduktID = sds.v98 and sds.Released = 1 and lower(sds.v99) = @aoLang and lower(sds.type) = '3_clp'
WHERE ISNULL(rms.Supplier, 0) = CASE
WHEN ISNULL(@p1, 0) > 0
THEN @p1
ELSE ISNULL(rms.Supplier, 0)
END; | Lists all chemicals on a specific location. | Delete | Edit |
| 207 | Focus report, one hierarchy element | ReportsPage | 20 | AlphaOmegaSuperUsers;AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT
Product,
SupplierName,
SubstanceOnCancerList = CAST(MAX(a.SubstanceOnCancerList) AS BIT),
SubstanceAllergy = CAST(MAX(a.SubstanceAllergy) AS BIT),
SubstanceOnPregnantList = CAST(MAX(a.SubstanceOnPregnantList) AS BIT),
ProductToxic = CAST(MAX(a.ProductToxic) AS BIT),
Usage,
ProductNumber
FROM (
SELECT DISTINCT
Product = p.Produktnavn,
FatherId = p.ProduktID,
ProductToxic = CASE
WHEN (
(
p.klassificering LIKE '%H300%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H310%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H330%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H301%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H301%' or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H311%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H311%' or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H331%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H331%' or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H370%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H370%')
)
OR (
p.klassificering LIKE '%H350%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H350%')
)
OR (
p.klassificering LIKE '%H360%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H360%')
)
OR (
p.klassificering LIKE '%H340%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H340%')
)
)
THEN 1
ELSE 0
END,
SubstanceOnCancerList = CASE
WHEN (
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%'
)
AND (
(
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR p.klassificering LIKE '%CARC%'
OR p.klassificering LIKE '%muta%'
)
THEN 1
ELSE 0
END,
SubstanceAllergy = CASE
WHEN (
(ISNULL(ps.Amount, 0) >= ISNULL(sc.H334, 200))
OR (ISNULL(ps.Amount, 0) >= ISNULL(sc.H317, 200))
)
OR (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H334, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H334, 0))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H317, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H317, 0))
)
)
THEN 1
ELSE 0
END,
SubstanceOnPregnantList = CASE
WHEN (
(
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
(
p.klassificering LIKE '%H341%'
OR p.klassificering LIKE '%H350%'
OR p.klassificering LIKE '%H351%'
OR p.klassificering LIKE '%H360%'
OR p.klassificering LIKE '%H361%'
OR p.klassificering LIKE '%H350i%'
OR p.klassificering LIKE '%H370%'
OR p.klassificering LIKE '%H371%'
OR p.klassificering LIKE '%H372%'
OR p.klassificering LIKE '%H373%'
OR p.klassificering LIKE '%H340%'
OR (
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR sub.[substansklass_GHS] LIKE '%H341%'
OR sub.[substansklass_GHS] LIKE '%H350%'
OR sub.[substansklass_GHS] LIKE '%H351%'
OR sub.[substansklass_GHS] LIKE '%H360%'
OR sub.[substansklass_GHS] LIKE '%H361%'
OR sub.[substansklass_GHS] LIKE '%H370%'
OR sub.[substansklass_GHS] LIKE '%H371%'
OR sub.[substansklass_GHS] LIKE '%H372%'
OR sub.[substansklass_GHS] LIKE '%H373%'
)
OR (
p.klassificering LIKE '%H310%'
OR p.klassificering LIKE '%H311%'
OR p.klassificering LIKE '%H312%'
OR p.klassificering LIKE '%H362%'
OR (
sub.[DK Oploesningsmiddel] = 1
AND ps.Amount >= 1
OR sub.DKGraensevaerdiAnm LIKE '%H%'
)
OR sub.[substansklass_GHS] LIKE '%H310%'
OR sub.[substansklass_GHS] LIKE '%H311%'
OR sub.[substansklass_GHS] LIKE '%H312%'
OR sub.[substansklass_GHS] LIKE '%H362%'
)
OR (
sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'
)
OR (
sub.[CAS nr] IN (
'89997-63-7',
'66603-10-9',
'37353-75-6',
'94-26-8',
'2312-35-8',
'71868-10-5',
'80844-07-1',
'89997-63-7',
'420-04-2',
'64-18-6',
'10028-15-6',
'108-46-3',
'05/09/7446',
'3319-31-1',
'83834-59-7',
'38083-17-9',
'71617-10-2',
'61788-44-1',
'115-86-6',
'10222-01-2',
'130328-19-7',
'75-21-8',
'1222-05-5',
'128-37-0',
'80-54-6',
'1453-58-3',
'77-40-7',
'84-61-7',
'265647-11-8',
'130328-20-0',
'137-26-8',
'35554-44-0',
'53988-10-6',
'61617-00-3',
'121158-58-5',
'80-05-7',
'21850-44-2',
'97416-84-7',
'96-76-4',
'118-56-9',
'13595-25-0',
'36861-47-9',
'78-59-1',
'80-05-7',
'7790-98-9',
'75-09-2',
'68479-98-1',
'7601-89-0',
'106-44-5',
'77-90-7',
'5384-21-4',
'95-14-7',
'80-08-0',
'628-96-6',
'693-21-0',
'108-46-3',
'57583-54-7',
'15087-24-8',
'98-54-4',
'80-46-6',
'17527-29-6',
'2144-53-8',
'6864-37-5',
'98-95-3',
'100-21-0',
'77-94-1',
'79-94-7',
'25068-38-6',
'119-36-8',
'131-57-7',
'288-88-0',
'118-82-1',
'96-69-5',
'80-09-1',
'330-54-1',
'99-76-3',
'94-13-3',
'1634-04-4',
'25013-16-5',
'137-30-4'
)
AND sub.[CAS nr] NOT IN ('64-17-5')
)
OR sub.[DK Oploesningsmiddel] = 1
)
)
THEN 1
ELSE 0
END,
SupplierName = sup.SupplierName,
Usage = a.Anvendelsesområde,
p.[Dessin nr] as ProductNumber
FROM APB_Brugssted_RM_TBL apb
INNER JOIN ProduktTabel p ON apb.ProduktID = p.ProduktID
LEFT JOIN RM_supplier rms ON p.ProduktID = rms.ProduktID
LEFT JOIN SupplierTBL sup ON rms.Supplier = sup.supplierID
INNER JOIN T_Categories c ON apb.category_amount_year_unit_id = c.id
INNER JOIN ProduktSubstansTabel ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel sub ON ps.SubstansID = sub.SubstansID
INNER JOIN KlassifikatiONsTabel_GHS sc ON sub.SubstansID = sc.SubstansID
left outer join KlassifikationsTabel_RM_CLP kc on kc.ProduktID = p.ProduktID
INNER JOIN T_Hierarchys h ON apb.t_hierarchy_id = h.id and h.id = @p0
LEFT JOIN Anvendelsesområde_RM a ON a.AnvendelsesområdeID = p.AnvendelsesområdeID
WHERE p.Aktiv = 1
AND (
ISNULL(rms.Supplier, 0) = CASE
WHEN ISNULL(@p1, 0) > 0
THEN @p1
ELSE ISNULL(rms.Supplier, 0)
END
)
-- Allergy
AND (
(
(
(ISNULL(ps.Amount, 0) >= ISNULL(sc.H334, 200))
OR (ISNULL(ps.Amount, 0) >= ISNULL(sc.H317, 200))
)
OR (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H334, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H334, 0))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H317, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H317, 0))
)
)
)
-- Toxic
OR (
(
p.klassificering LIKE '%H300%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H310%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H330%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H301%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H301%' or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H311%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H311%' or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H331%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H331%' or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H370%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H370%')
)
OR (
p.klassificering LIKE '%H350%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H350%')
)
OR (
p.klassificering LIKE '%H360%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H360%')
)
OR (
p.klassificering LIKE '%H340%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H340%')
)
)
-- Pregnant
OR (
(
p.klassificering LIKE '%H341%'
OR p.klassificering LIKE '%H350%'
OR p.klassificering LIKE '%H351%'
OR p.klassificering LIKE '%H360%'
OR p.klassificering LIKE '%H361%'
OR p.klassificering LIKE '%H350i%'
OR p.klassificering LIKE '%H370%'
OR p.klassificering LIKE '%H371%'
OR p.klassificering LIKE '%H372%'
OR p.klassificering LIKE '%H373%'
OR p.klassificering LIKE '%H340%'
OR (
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR sub.[substansklass_GHS] LIKE '%H341%'
OR sub.[substansklass_GHS] LIKE '%H350%'
OR sub.[substansklass_GHS] LIKE '%H351%'
OR sub.[substansklass_GHS] LIKE '%H360%'
OR sub.[substansklass_GHS] LIKE '%H361%'
OR sub.[substansklass_GHS] LIKE '%H370%'
OR sub.[substansklass_GHS] LIKE '%H371%'
OR sub.[substansklass_GHS] LIKE '%H372%'
OR sub.[substansklass_GHS] LIKE '%H373%'
)
OR (
p.klassificering LIKE '%H310%'
OR p.klassificering LIKE '%H311%'
OR p.klassificering LIKE '%H312%'
OR p.klassificering LIKE '%H362%'
OR (
sub.[DK Oploesningsmiddel] = 1
AND ps.Amount >= 1
OR sub.DKGraensevaerdiAnm LIKE '%H%'
)
OR sub.[substansklass_GHS] LIKE '%H310%'
OR sub.[substansklass_GHS] LIKE '%H311%'
OR sub.[substansklass_GHS] LIKE '%H312%'
OR sub.[substansklass_GHS] LIKE '%H362%'
)
OR (
sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'
)
OR (
sub.[CAS nr] IN (
'89997-63-7',
'66603-10-9',
'37353-75-6',
'94-26-8',
'2312-35-8',
'71868-10-5',
'80844-07-1',
'89997-63-7',
'420-04-2',
'64-18-6',
'10028-15-6',
'108-46-3',
'05/09/7446',
'3319-31-1',
'83834-59-7',
'38083-17-9',
'71617-10-2',
'61788-44-1',
'115-86-6',
'10222-01-2',
'130328-19-7',
'75-21-8',
'1222-05-5',
'128-37-0',
'80-54-6',
'1453-58-3',
'77-40-7',
'84-61-7',
'265647-11-8',
'130328-20-0',
'137-26-8',
'35554-44-0',
'53988-10-6',
'61617-00-3',
'121158-58-5',
'80-05-7',
'21850-44-2',
'97416-84-7',
'96-76-4',
'118-56-9',
'13595-25-0',
'36861-47-9',
'78-59-1',
'80-05-7',
'7790-98-9',
'75-09-2',
'68479-98-1',
'7601-89-0',
'106-44-5',
'77-90-7',
'5384-21-4',
'95-14-7',
'80-08-0',
'628-96-6',
'693-21-0',
'108-46-3',
'57583-54-7',
'15087-24-8',
'98-54-4',
'80-46-6',
'17527-29-6',
'2144-53-8',
'6864-37-5',
'98-95-3',
'100-21-0',
'77-94-1',
'79-94-7',
'25068-38-6',
'119-36-8',
'131-57-7',
'288-88-0',
'118-82-1',
'96-69-5',
'80-09-1',
'330-54-1',
'99-76-3',
'94-13-3',
'1634-04-4',
'25013-16-5',
'137-30-4'
)
AND sub.[CAS nr] NOT IN ('64-17-5')
)
OR sub.[DK Oploesningsmiddel] = 1
)
-- Cancer
OR (
(
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%'
)
AND ps.Amount >= 0.1
)
)
) a
GROUP BY
a.Product,
a.SupplierName,
a.Usage,
a.ProductNumber | List containing all chemicals that should given special attention (riskfactors R/H or contains dangerous metals such as HG,PB or CD), for a given location.
...
WHERE p.Aktiv = 1
AND (ISNULL(rms.Supplier,0) = CASE
WHEN ISNULL(@p1,0) >0 THEN @p1
ELSE ISNULL(rms.Supplier,0)
END)
-- Allergy
AND ((((ISNULL(ps.Amount,0) >= ISNULL(sc.H334,200)) OR (ISNULL(ps.Amount,0) >= ISNULL(sc.H317,200)))
OR (((ISNULL(sc.H334,0) > 0) AND (ISNULL(ps.Amount,0) >= (ISNULL(sc.H334,1100) / 10)) AND (ISNULL(ps.Amount,0) < ISNULL(sc.H334,0)))
OR ((ISNULL(sc.H317,0) > 0) AND (ISNULL(ps.Amount,0) >= (ISNULL(sc.H317,1100) / 10)) AND (ISNULL(ps.Amount,0) < ISNULL(sc.H317,0)))))
-- Toxic
OR ((p.klassificering LIKE '%H300%' AND sub.Substansklass_GHS LIKE '%H300%')
OR (p.klassificering LIKE '%H310%' AND sub.Substansklass_GHS LIKE '%H310%')
OR (p.klassificering LIKE '%H330%' AND sub.Substansklass_GHS LIKE '%H330%')
OR (p.klassificering LIKE '%H301%' AND sub.Substansklass_GHS LIKE '%H301%')
OR (p.klassificering LIKE '%H311%' AND sub.Substansklass_GHS LIKE '%H311%')
OR (p.klassificering LIKE '%H331%' AND sub.Substansklass_GHS LIKE '%H331%')
OR (p.klassificering LIKE '%H370%' AND sub.Substansklass_GHS LIKE '%H370%')
OR (p.klassificering LIKE '%H350%' AND sub.Substansklass_GHS LIKE '%H350%')
OR (p.klassificering LIKE '%H360%' AND sub.Substansklass_GHS LIKE '%H360%')
OR (p.klassificering LIKE '%H340%' AND sub.Substansklass_GHS LIKE '%H340%')
OR (p.klassificering LIKE '%R26%' AND sub.Substansklass LIKE '%R26%')
OR (p.klassificering LIKE '%R27%' AND sub.Substansklass LIKE '%R27%')
OR (p.klassificering LIKE '%R28%' AND sub.Substansklass LIKE '%R28%')
OR (p.klassificering LIKE '%R39%' AND sub.Substansklass LIKE '%R39%')
OR (p.klassificering LIKE '%R23%' AND sub.Substansklass LIKE '%R23%')
OR (p.klassificering LIKE '%R24%' AND sub.Substansklass LIKE '%R24%')
OR (p.klassificering LIKE '%R25%' AND sub.Substansklass LIKE '%R25%')
OR (p.klassificering LIKE '%R45%' AND sub.Substansklass LIKE '%R45%')
OR (p.klassificering LIKE '%R49%' AND sub.Substansklass LIKE '%R49%')
OR (p.klassificering LIKE '%R46%' AND sub.Substansklass LIKE '%R46%')
OR (p.klassificering LIKE '%R60%' AND sub.Substansklass LIKE '%R60%')
OR (p.klassificering LIKE '%R61%' AND sub.Substansklass LIKE '%R61%'))
-- Pregnant
OR ((sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub,'') = '')
AND (sub.Substansklass_GHS LIKE '%H340%'
OR sub.Substansklass_GHS LIKE '%H341%'
OR sub.Substansklass_GHS LIKE '%H350%'
OR sub.Substansklass_GHS LIKE '%H351%'
OR sub.Substansklass_GHS LIKE '%H360%'
OR sub.Substansklass_GHS LIKE '%H361%'
OR sub.Substansklass_GHS LIKE '%H370%'
OR sub.Substansklass_GHS LIKE '%H371%'
OR sub.Substansklass_GHS LIKE '%H372%'
OR sub.Substansklass_GHS LIKE '%H373%'
OR sub.Substansklass_GHS LIKE '%H312%'
OR sub.Substansklass LIKE '%R39%'
OR sub.Substansklass LIKE '%R40%'
OR sub.Substansklass LIKE '%R45%'
OR sub.Substansklass LIKE '%R46%'
OR sub.Substansklass LIKE '%R48%'
OR sub.Substansklass LIKE '%R49%'
OR sub.Substansklass LIKE '%R60%'
OR sub.Substansklass LIKE '%R61%'
OR sub.Substansklass LIKE '%R62%'
OR sub.Substansklass LIKE '%R63%'
OR sub.Substansklass LIKE '%R64%'
OR sub.Substansklass LIKE '%R68%'
OR sub.[DK Kraeftlisten] = 1
OR (sub.[DK Oploesningsmiddel] = 1
AND sub.DKGraensevaerdiAnm LIKE '%H%'
AND ps.Amount >= 1)
OR sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'))
-- Cancer
OR ((sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub,'') = '')
AND (sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%'))
| Delete | Edit |
| 162 | Allergy report, one hierarchy element | ReportsPage | 40 | AlphaOmegaSuperUsers;AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT Product = p.Produktnavn,
s.[CAS nr] as SubstansCas,
SubstanceAllergy = s.[Navn Systematisk],
SubstanceConcentrationInterval = CASE
WHEN ps.Interval = '0'
THEN CASE
WHEN (
ps.Amount > 0
AND ps.Amount < 0.001
)
THEN '<0,001'
WHEN (
ps.Amount >= 0.001
AND ps.Amount < 0.01
)
THEN '<0,01'
WHEN (
ps.Amount >= 0.01
AND ps.Amount < 0.1
)
THEN '<0,1'
WHEN (
ps.Amount >= 0.1
AND ps.Amount < 1
)
THEN '<1'
WHEN (
ps.Amount >= 1
AND ps.Amount < 5
)
THEN '1-5'
WHEN (
ps.Amount >= 5
AND ps.Amount < 15
)
THEN '5-15'
WHEN (
ps.Amount >= 15
AND ps.Amount < 25
)
THEN '15-25'
WHEN (
ps.Amount >= 25
AND ps.Amount < 40
)
THEN '25-40'
WHEN (
ps.Amount >= 40
AND ps.Amount < 60
)
THEN '40-60'
WHEN (
ps.Amount >= 60
AND ps.Amount < 80
)
THEN '60-80'
WHEN (
ps.Amount >= 80
AND ps.Amount < 95
)
THEN '80-95'
WHEN (
ps.Amount >= 95
AND ps.Amount <= 100
)
THEN '95-100'
ELSE ps.Interval
END
ELSE ps.Interval
END,
ProductAllergy = IIF((
CASE
WHEN ((ISNULL(ps.Amount, 0) >= ISNULL(sc.H317, 200)))
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END
) = 1, 'X', ''),
ProductRespAllergy = IIF((
CASE
WHEN ((ISNULL(ps.Amount, 0) >= ISNULL(sc.H334, 200)))
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END
) = 1, 'X', ''),
ProductAllergyReaction = IIF((
CASE
-- when ProductAllergy is true, return 0 for this column
WHEN (
(ISNULL(ps.Amount, 0) >= ISNULL(sc.H334, 200))
OR (ISNULL(ps.Amount, 0) >= ISNULL(sc.H317, 200))
)
THEN CAST(0 AS BIT)
WHEN (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H334, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H334, 0))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H317, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H317, 0))
)
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END
) = 1, 'X', ''),
SubstanceAllergyProductAllergy = IIF((
CASE
-- if ProductAllergyReaction is true, return 0 in this column
WHEN (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H334, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H334, 0))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H317, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H317, 0))
)
)
THEN CAST(0 AS BIT)
WHEN (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) < (ISNULL(sc.H334, 0) / 10))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) < (ISNULL(sc.H317, 0) / 10))
)
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END
) = 1, 'X', ''),
p.[Dessin nr] as ProductNumber
FROM APB_Brugssted_RM_TBL apb
INNER JOIN ProduktTabel p ON apb.t_hierarchy_id = @p0
AND apb.ProduktID = p.ProduktID
INNER JOIN T_Categories c ON apb.category_amount_year_unit_id = c.id
INNER JOIN ProduktSubstansTabel ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel s ON ps.SubstansID = s.SubstansID
INNER JOIN T_Hierarchys h ON apb.t_hierarchy_id = h.id
INNER JOIN KlassifikatiONsTabel_GHS sc ON s.SubstansID = sc.SubstansID
WHERE p.Aktiv = 1
AND (
(ISNULL(ps.Amount, 0) >= ISNULL(sc.H334, 200))
OR (ISNULL(ps.Amount, 0) >= ISNULL(sc.H317, 200))
)
OR (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H334, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H334, 0))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H317, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H317, 0))
)
)
OR (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) < (ISNULL(sc.H334, 0) / 10))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) < (ISNULL(sc.H317, 0) / 10))
)
) | List containing all, in the database, active chemicals, with allergenic properties for a location | Delete | Edit |
| 219 | Fire report, one hierarchy element | ReportsPage | 50 | AlphaOmegaSuperUsers;AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
Product = p.Produktnavn,
MixedWithWater = CAST(ISNULL(p.BlandbaH2O,0) AS BIT),
FlameValue = CAST(p.Flammepunkt AS INT),
FlameClass = CASE
WHEN (p.[Fysisk tilstand] <> 'Flydende' and p.[Fysisk tilstand] <> 'Gel') THEN '-'
WHEN p.Flammepunkt < 23 THEN 'I - '
WHEN p.Flammepunkt >= 23 AND p.Flammepunkt <= 60 THEN 'II -'
WHEN p.Flammepunkt > 60 AND p.Flammepunkt <= 93 THEN 'III -'
ELSE '-'
END +
CASE
WHEN (p.[Fysisk tilstand] <> 'Flydende' and p.[Fysisk tilstand] <> 'Gel') THEN ''
WHEN ISNULL(p.BlandbaH2O,0) = 1 THEN '2'
ELSE '1'
END,
p.[Dessin nr] as ProductNumber
from APB_Brugssted_RM_TBL apb
join ProduktTabel p
on apb.ProduktID = p.ProduktID
join T_Hierarchys h
on apb.t_hierarchy_id = @p0 AND apb.t_hierarchy_id = h.id
where p.Aktiv = 1
AND p.Flammepunkt IS NOT null
AND p.Flammepunkt < 99
AND (p.klassificering LIKE '%H220%'
OR p.klassificering LIKE '%H221%'
OR p.klassificering LIKE '%H222%'
OR p.klassificering LIKE '%H223%'
OR p.klassificering LIKE '%H224%'
OR p.klassificering LIKE '%H225%'
OR p.klassificering LIKE '%H226%'
OR p.klassificering LIKE '%H228%'
OR p.klassificering LIKE '%H241%'
OR p.klassificering LIKE '%H242%'
OR p.klassificering LIKE '%H250%'
OR p.klassificering LIKE '%H251%'
OR p.klassificering LIKE '%H252%'
OR p.klassificering LIKE '%H260%'
OR p.klassificering LIKE '%H261%'
OR p.klassificering LIKE '%R10%'
OR p.klassificering LIKE '%R11%'
OR p.klassificering LIKE '%R12%')
AND (p.[Fysisk tilstand] like 'Flydende' or p.[Fysisk tilstand] like 'Gel')
| List containing all, in the database, active flammable chemicals for a location. | Delete | Edit |
| 168 | Handling report, one hierarchy element | ReportsPage | 60 | AlphaOmegaSuperUsers;AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
p.ProduktId,
Product = p.Produktnavn + CASE
WHEN ISNULL(anv.APV_ES_navn,'') <> '' THEN ' // ' + anv.APV_ES_navn
ELSE ''
END,
ProductGloves = (select * from GetGlovesPPEList(p.ProduktId, anv.APV_anvendelse_ID)),
Inhalation = (select * from GetInhalationPPEList(p.ProduktId,anv.APV_anvendelse_ID)),
ProductGlasses = (select * from GetGooglesPPEList(p.ProduktId,anv.APV_anvendelse_ID)),
BodyProtection = (select * from GetBodyPPEList(p.ProduktId,anv.APV_anvendelse_ID)),
p.[Dessin nr] as ProductNumber
FROM APB_Brugssted_RM_TBL apb
JOIN ProduktTabel p ON p.ProduktID = apb.ProduktID
JOIN EtiketTBL e ON e.ProduktID = p.ProduktID
JOIN T_Hierarchys h ON apb.t_hierarchy_id = @p0 AND h.id = apb.t_hierarchy_id
LEFT JOIN ES_Produkt_TBL esprod ON esprod.ProduktID = p.ProduktID
LEFT JOIN APV_anvendelseTBL anv ON anv.APV_anvendelse_ID = esprod.ES_ID
WHERE ISNULL(p.Aktiv,0) = 1
order by Product | List all handling requirements for all active products on the given location. | Delete | Edit |
| 216 | Garbage report, one hierarchy | ReportsPage | 70 | AlphaOmegaSuperUsers;AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
Product,
GarbageGroup,
GarbageLabel = CASE WHEN Epoxy > 0 THEN 'Epoxy ' ELSE '' END + CASE WHEN Iso > 0 THEN 'Isocyanat ' ELSE '' END + CASE WHEN Cancer > 0 THEN 'Kræft' ELSE '' END,
WasteHandling,
ProductNumber,
UN,
PG,
CLASS,
PSN,
DangerousWaste,
CAS_nr,
Substances,
DangerousGoods
FROM (
SELECT DISTINCT
Product = p.Produktnavn,
GarbageGroup = w.AffaldDK,
FatherId = p.ProduktID,
p.Aktiv,
SUM(CAST(ISNULL(sub.epoxyharpiks,0) AS INT) + CAST(ISNULL(sub.EpoxMon,0) AS INT)) AS Epoxy,
SUM(CAST(ISNULL(sub.IsocyanteMon,0) AS INT) + CAST(ISNULL(sub.PraepolIsocyanate,0) AS INT)) AS Iso,
SUM(CAST(ISNULL(sub.[DK Kraeftlisten],0) AS INT)) AS Cancer,
isnull(wp.DK,'') as WasteHandling,
p.[Dessin nr] as ProductNumber,
isnull(t.UN, '') AS UN,
isnull(t.Pck_grp, '') AS PG,
isnull(t.FareKlass, '') AS CLASS,
isnull(t.ProperShipppingADR, '') AS PSN,
convert(bit, ISNULL((select top 1 case when v94 like '%'+ (select top 1 PhraseAPB collate Danish_Norwegian_CI_AS from PhraseTbl where PhraseID = 52) + '%' then 1 else 0 end from SDSVersionTbl where v98 = p.ProduktID and Released = 1 and type = '3_CLP' and v99 = 'DK'),0)) as DangerousWaste,
convert(bit, ISNULL((select top 1 case when v28 like '%'+ (select top 1 PhraseAPB collate Danish_Norwegian_CI_AS from PhraseTbl where PhraseID = 159) + '%' then 1 else 0 end from SDSVersionTbl where v98 = p.ProduktID and Released = 1 and type = '3_CLP' and v99 = 'DK'),0)) as DangerousGoods,
(STUFF((SELECT (', ' + sub.[CAS nr])
FROM ProduktSubstansTabel prs
inner join SubstansTabel sub on sub.SubstansID = prs.SubstansID
WHERE
prs.ProduktID = p.ProduktID
FOR XML PATH('')), 1, 2, '')) AS CAS_nr,
(STUFF((SELECT (', ' + sub.[Navn Systematisk] + ':' + prs.Interval)
FROM ProduktSubstansTabel prs
inner join SubstansTabel sub on sub.SubstansID = prs.SubstansID
WHERE
prs.ProduktID = p.ProduktID
FOR XML PATH('')), 1, 2, '')) AS Substances
FROM
APB_Brugssted_RM_TBL apb
JOIN ProduktTabel p ON apb.t_hierarchy_id = @p0 AND apb.ProduktID = p.ProduktID
JOIN ProduktSubstansTabel ps ON p.ProduktID = ps.ProduktID JOIN SubstansTabel sub ON ps.SubstansID = sub.SubstansID
JOIN T_Hierarchys h ON apb.t_hierarchy_id = h.id
JOIN WastePhraseProductTBL wpp ON p.ProduktID = wpp.ProductID
LEFT JOIN WasteCatTBL w ON wpp.WasteGroup = w.ID
left outer join WastePhaseTBL wp on wp.WastePhaseID = wpp.WastePhraseID
LEFT OUTER JOIN Transport_ADR_RID_RM AS t ON t.ProduktID = p.ProduktID
WHERE
isnull(w.AffaldDK,'-') <> '-'
GROUP BY p.ProduktID, p.Produktnavn, w.AffaldDK, p.Aktiv, wp.DK, p.[Dessin nr], t.un, t.Pck_grp, t.FareKlass, t.ProperShipppingADR) a | Garbage report, one hierarchy element. Lists all, in the database, active chemicals with garbage handling information for a location. | Delete | Edit |
| 205 | Toxic report, one hierarchy element | ReportsPage | 70 | AlphaOmegaSuperUsers;AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT Product = p.Produktnavn,
sub.[CAS nr] as SubstansCas,
SubstanceToxic =
case when isnull(kc.Ingen_beregning,0) = 1 then '-' else sub.[Navn Systematisk] end,
MustLockup = CAST(1 AS BIT),
SecurityResponsible = CASE
WHEN (
(
p.klassificering LIKE '%H300%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H310%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H330%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H301%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H301%' or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H311%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H311%' or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H331%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H331%' or sub.Substansklass_GHS LIKE '%H330%')
)
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END,
ReportTheft = CASE
WHEN (
(
p.klassificering LIKE '%H300%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H310%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H330%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H301%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H301%' or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H311%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H311%' or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H331%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H331%' or sub.Substansklass_GHS LIKE '%H330%')
)
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END,
apb.Departments,
p.[Dessin nr] as ProductNumber
FROM APB_Brugssted_RM_TBL apb
INNER JOIN ProduktTabel p ON apb.t_hierarchy_id = @p0
AND apb.ProduktID = p.ProduktID
left outer join KlassifikationsTabel_RM_CLP kc on kc.ProduktID = p.ProduktID
INNER JOIN ProduktSubstansTabel ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel sub ON ps.SubstansID = sub.SubstansID
INNER JOIN T_Hierarchys h ON apb.t_hierarchy_id = h.id
WHERE p.Aktiv = 1
AND (
(
p.klassificering LIKE '%H300%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H310%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H330%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H301%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H301%' or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H311%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H311%' or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H331%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H331%' or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H370%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H370%')
)
OR (
p.klassificering LIKE '%H350%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H350%')
)
OR (
p.klassificering LIKE '%H360%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H360%')
)
OR (
p.klassificering LIKE '%H340%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H340%')
)
)
| List containing all, in the database, active chemicals with toxic properties for a chosen location.
The list contains chemicals which are danger labelled with one or more of the following H-sentences:
H300, H310, H330, H301, H311, H331, H370, H360, H350, H340 | Delete | Edit |
| 225 | Cancer list, one hierarchy element | ReportsPage | 80 | AlphaOmegaSuperUsers;AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT Product = p.Produktnavn,
SubstanceRisk = sub.[Navn Systematisk],
sub.[CAS nr] as SubstansCas,
SubstanceConcentrationInterval = CASE ps.Interval
WHEN '0'
THEN CASE
WHEN (
ps.Amount > 0
AND ps.Amount < 0.001
)
THEN '<0,001'
WHEN (
ps.Amount >= 0.001
AND ps.Amount < 0.01
)
THEN '<0,01'
WHEN (
ps.Amount >= 0.01
AND ps.Amount < 0.1
)
THEN '<0,1'
WHEN (
ps.Amount >= 0.1
AND ps.Amount < 1
)
THEN '<1'
WHEN (
ps.Amount >= 1
AND ps.Amount < 5
)
THEN '1-5'
WHEN (
ps.Amount >= 5
AND ps.Amount < 15
)
THEN '5-15'
WHEN (
ps.Amount >= 15
AND ps.Amount < 25
)
THEN '15-25'
WHEN (
ps.Amount >= 25
AND ps.Amount < 40
)
THEN '25-40'
WHEN (
ps.Amount >= 40
AND ps.Amount < 60
)
THEN '40-60'
WHEN (
ps.Amount >= 60
AND ps.Amount < 80
)
THEN '60-80'
WHEN (
ps.Amount >= 80
AND ps.Amount < 95
)
THEN '80-95'
WHEN (
ps.Amount >= 95
AND ps.Amount <= 100
)
THEN '95-100'
ELSE ps.Interval
END
ELSE ps.Interval
END,
SubstanceOnCancerListGeneralDemands = CAST(1 AS BIT),
SpecialDemands = CASE
WHEN sub.CancerDK_17 = 1
OR sub.CancerDK_27 = 1
OR sub.CancerDK_29_32 = 1
OR sub.CancerDK_33 = 1
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END,
apb.Departments,
p.[Dessin nr] as ProductNumber
FROM APB_Brugssted_RM_TBL apb
INNER JOIN ProduktTabel p ON apb.t_hierarchy_id = @p0
AND apb.ProduktID = p.ProduktID
INNER JOIN ProduktSubstansTabel ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel sub ON ps.SubstansID = sub.SubstansID
INNER JOIN T_Hierarchys h ON apb.t_hierarchy_id = h.id
INNER JOIN dbo.HierarchyPaths() ppp ON ppp.Id = h.id
WHERE p.Aktiv = 1
AND (
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%'
)
AND
(
(((sub.[DK Kraeftlisten] = 1 and ps.Amount >= 0.1) OR p.klassificering LIKE '%CARC. 1%' OR p.klassificering like '%muta%'))
OR
(sub.[DK Kraeftlisten] <> 1 and p.klassificering LIKE '%CARC. 2%' and ps.Amount >= 1.0)
)
| List containing all, in the database, active chemicals with carcinogenic properties for a single hierarchy. The chemical is on the list, if the chemical is:
- Included by the Statutory order on prevention of the risk of cancer, appendix 1A, regardless danger labelling OR
- Is classified with Muta. 1A: H340 or Muta 1B; H340 OR
- Is classified with on of the following: Carc. 1A; H350 eller H350i, Carc. 1B; H350 eller H350i, Carc. 2; H351 | Delete | Edit |
| 228 | Pregnancy report, one hierarchy element | ReportsPage | 90 | AlphaOmegaSuperUsers;AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
selection.Product,
selection.SubstanceRisk,
selection.SubstanceConcentrationInterval,
longterm,
skincontact,
heavymetals,
endocrinedisruptors,
inhalation,
SubstansCas,
ProductNumber
FROM (
SELECT DISTINCT
p.Produktnavn AS Product,
sub.[Navn Systematisk] AS SubstanceRisk,
sub.[substansklass_GHS] AS SubstanceClassification,
sub.[CAS nr] AS SubstansCas,
sub.strukturformel AS SubstansFormel,
sub.[DK Kraeftlisten],
sub.[DK Oploesningsmiddel],
sub.DKGraensevaerdiAnm,
ps.Amount,
CASE ps.Interval
WHEN '0'
THEN CASE
WHEN (
ps.Amount > 0
AND ps.Amount < 0.001
)
THEN '<0,001'
WHEN (
ps.Amount >= 0.001
AND ps.Amount < 0.01
)
THEN '<0,01'
WHEN (
ps.Amount >= 0.01
AND ps.Amount < 0.1
)
THEN '<0,1'
WHEN (
ps.Amount >= 0.1
AND ps.Amount < 1
)
THEN '<1'
WHEN (
ps.Amount >= 1
AND ps.Amount < 5
)
THEN '1-5'
WHEN (
ps.Amount >= 5
AND ps.Amount < 15
)
THEN '5-15'
WHEN (
ps.Amount >= 15
AND ps.Amount < 25
)
THEN '15-25'
WHEN (
ps.Amount >= 25
AND ps.Amount < 40
)
THEN '25-40'
WHEN (
ps.Amount >= 40
AND ps.Amount < 60
)
THEN '40-60'
WHEN (
ps.Amount >= 60
AND ps.Amount < 80
)
THEN '60-80'
WHEN (
ps.Amount >= 80
AND ps.Amount < 95
)
THEN '80-95'
WHEN (
ps.Amount >= 95
AND ps.Amount <= 100
)
THEN '95-100'
ELSE ps.Interval
END
ELSE ps.Interval
END AS SubstanceConcentrationInterval,
CAST(CASE
WHEN (
(
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR sub.[substansklass_GHS] LIKE '%H340%'
OR sub.[substansklass_GHS] LIKE '%H341%'
OR sub.[substansklass_GHS] LIKE '%H350%'
OR sub.[substansklass_GHS] LIKE '%H350i%'
OR sub.[substansklass_GHS] LIKE '%H351%'
OR sub.[substansklass_GHS] LIKE '%H360%'
OR sub.[substansklass_GHS] LIKE '%H361%'
OR sub.[substansklass_GHS] LIKE '%H370%'
OR sub.[substansklass_GHS] LIKE '%H371%'
OR sub.[substansklass_GHS] LIKE '%H372%'
OR sub.[substansklass_GHS] LIKE '%H373%'
OR sub.[substansklass_GHS] LIKE '%EUH440%'
OR sub.[substansklass_GHS] LIKE '%EUH441%'
)
THEN 1
ELSE 0
END AS BIT) AS longterm,
CAST(CASE
WHEN (
(
sub.[DK Oploesningsmiddel] = 1
AND ps.Amount >= 1
OR sub.DKGraensevaerdiAnm LIKE '%H%'
)
OR sub.[substansklass_GHS] LIKE '%H310%'
OR sub.[substansklass_GHS] LIKE '%H311%'
OR sub.[substansklass_GHS] LIKE '%H312%'
OR sub.[substansklass_GHS] LIKE '%H362%'
)
THEN 1
ELSE 0
END AS BIT) AS skincontact,
CAST(CASE
WHEN (
sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'
)
THEN 1
ELSE 0
END AS BIT) AS heavymetals,
CAST(CASE
WHEN (
sub.[CAS nr] IN (
'36861-47-9',
'95342-41-9',
'852541-21-0',
'741687-98-9',
'852541-30-1',
'852541-25-4',
'1782069-81-1',
'27459-10-5',
'77-40-7',
'80-05-7',
'85-68-7',
'117-81-7',
'94-26-8',
'67-97-0',
'84-74-2',
'84-61-7',
'84-69-5',
'4247-02-3',
'8018-01-7',
'74499-35-7',
'210555-94-5',
'27147-75-7',
'121158-58-5',
'57427-55-1'
)
)
THEN 1
ELSE 0
END AS BIT) AS endocrinedisruptors,
CAST(CASE
WHEN (sub.[DK Oploesningsmiddel] = 1)
THEN 1
ELSE 0
END AS BIT) AS inhalation,
p.[Dessin nr] as ProductNumber
FROM APB_Brugssted_RM_TBL AS apb
INNER JOIN ProduktTabel AS p ON apb.ProduktID = p.ProduktID
INNER JOIN ProduktSubstansTabel AS ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel AS sub ON ps.SubstansID = sub.SubstansID
INNER JOIN T_Hierarchys AS h ON apb.t_hierarchy_id = h.id and h.id = @p0
WHERE p.[AnvendelsesområdeID] NOT IN (
24,
1040
)
AND p.Aktiv = 1
AND (
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
(
(
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR sub.[substansklass_GHS] LIKE '%H340%'
OR sub.[substansklass_GHS] LIKE '%H341%'
OR sub.[substansklass_GHS] LIKE '%H350%'
OR sub.[substansklass_GHS] LIKE '%H350i%'
OR sub.[substansklass_GHS] LIKE '%H351%'
OR sub.[substansklass_GHS] LIKE '%H360%'
OR sub.[substansklass_GHS] LIKE '%H361%'
OR sub.[substansklass_GHS] LIKE '%H370%'
OR sub.[substansklass_GHS] LIKE '%H371%'
OR sub.[substansklass_GHS] LIKE '%H372%'
OR sub.[substansklass_GHS] LIKE '%H373%'
OR sub.[substansklass_GHS] LIKE '%EUH440%'
OR sub.[substansklass_GHS] LIKE '%EUH441%'
)
OR (
(
sub.[DK Oploesningsmiddel] = 1
AND ps.Amount >= 1
OR sub.DKGraensevaerdiAnm LIKE '%H%'
)
OR sub.[substansklass_GHS] LIKE '%H310%'
OR sub.[substansklass_GHS] LIKE '%H311%'
OR sub.[substansklass_GHS] LIKE '%H312%'
OR sub.[substansklass_GHS] LIKE '%H362%'
)
OR (
sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'
)
OR (
sub.[substansklass_GHS] LIKE '%EUH380%'
OR sub.[substansklass_GHS] LIKE '%EUH381%'
)
OR (
sub.[CAS nr] IN (
'36861-47-9',
'95342-41-9',
'852541-21-0',
'741687-98-9',
'852541-30-1',
'852541-25-4',
'1782069-81-1',
'27459-10-5',
'77-40-7',
'80-05-7',
'85-68-7',
'117-81-7',
'94-26-8',
'67-97-0',
'84-74-2',
'84-61-7',
'84-69-5',
'4247-02-3',
'8018-01-7',
'74499-35-7',
'210555-94-5',
'27147-75-7',
'121158-58-5',
'57427-55-1'
)
)
OR sub.[DK Oploesningsmiddel] = 1
) AND NOT sub.[CAS nr] IN ('64-17-5')
) AS selection; | List containing all chemicals that are known to affect pregnancy/foetus, for a location.
...
WHERE p.[AnvendelsesområdeID] NOT IN (
24,
1040
)
AND p.Aktiv = 1
AND (
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
(
p.klassificering LIKE '%H340%'
OR p.klassificering LIKE '%H341%'
OR p.klassificering LIKE '%H350%'
OR p.klassificering LIKE '%H350i%'
OR p.klassificering LIKE '%H351%'
OR p.klassificering LIKE '%H360%'
OR p.klassificering LIKE '%H361%'
OR p.klassificering LIKE '%H370%'
OR p.klassificering LIKE '%H371%'
OR p.klassificering LIKE '%H372%'
OR p.klassificering LIKE '%H373%'
OR (
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR sub.[substansklass_GHS] LIKE '%H340%'
OR sub.[substansklass_GHS] LIKE '%H341%'
OR sub.[substansklass_GHS] LIKE '%H350%'
OR sub.[substansklass_GHS] LIKE '%H350i%'
OR sub.[substansklass_GHS] LIKE '%H351%'
OR sub.[substansklass_GHS] LIKE '%H360%'
OR sub.[substansklass_GHS] LIKE '%H361%'
OR sub.[substansklass_GHS] LIKE '%H370%'
OR sub.[substansklass_GHS] LIKE '%H371%'
OR sub.[substansklass_GHS] LIKE '%H372%'
OR sub.[substansklass_GHS] LIKE '%H373%'
)
OR (
(
sub.[DK Oploesningsmiddel] = 1
AND ps.Amount >= 1
OR sub.DKGraensevaerdiAnm LIKE '%H%'
)
OR sub.[substansklass_GHS] LIKE '%H310%'
OR sub.[substansklass_GHS] LIKE '%H311%'
OR sub.[substansklass_GHS] LIKE '%H312%'
OR sub.[substansklass_GHS] LIKE '%H362%'
)
OR (
sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'
)
OR (
sub.[CAS nr] IN (
'36861-47-9',
'95342-41-9',
'852541-21-0',
'741687-98-9',
'852541-30-1',
'852541-25-4',
'1782069-81-1',
'27459-10-5',
'77-40-7',
'80-05-7',
'85-68-7',
'117-81-7',
'94-26-8',
'67-97-0',
'84-74-2',
'84-61-7',
'84-69-5',
'4247-02-3',
'8018-01-7',
'74499-35-7',
'210555-94-5',
'27147-75-7',
'121158-58-5',
'57427-55-1'
)
)
OR sub.[DK Oploesningsmiddel] = 1
) AND NOT sub.[CAS nr] IN ('64-17-5') | Delete | Edit |
| 111 | Terror list, one hierarchy element | ReportsPage | 95 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT
Product = p.Produktnavn,
ProductClassification = p.klassificering
FROM ProduktTabel p
JOIN APB_Brugssted_RM_TBL apb
ON apb.t_hierarchy_id = @p0 AND p.ProduktID = apb.ProduktID
JOIN T_Hierarchys h
ON apb.t_hierarchy_id = h.id
WHERE p.klassificering LIKE '%H300%'
OR p.klassificering LIKE '%H301%'
OR p.klassificering LIKE '%H310%'
OR p.klassificering LIKE '%H311%'
OR p.klassificering LIKE '%H330%'
OR p.klassificering LIKE '%H331%'
| Lists all chemical named on the Terror list for a given location. | Delete | Edit |
| 214 | Laboratorie report, one hierarchy element | ReportsPage | 100 | AlphaOmegaSuperUsers;AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
Product = p.Produktnavn,
[Synonym] = p.etiketlink,
SupplierName = sup.SupplierName,
ProductClassification = p.klassificering,
ProductPhysicalForm = p.[Fysisk tilstand],
ProductStock = sto.LagertempDK,
GarbageGroup = w.AffaldDK,
Usage = a.Anvendelsesområde,
Room = apb.Lokale,
Cabinet = apb.Skab,
Shelf = apb.Hylde,
Other = apb.Andet,
p.[Dessin nr] as ProductNmber
FROM APB_Brugssted_RM_TBL apb
JOIN ProduktTabel p ON apb.t_hierarchy_id = @p0 AND apb.ProduktID = p.ProduktID
JOIN WastePhraseProductTBL wpp ON wpp.ProductID = p.ProduktID
JOIN Anvendelsesområde_RM a ON p.AnvendelsesområdeID = a.AnvendelsesområdeID
LEFT JOIN RM_supplier rms ON p.ProduktID = rms.ProduktID
LEFT JOIN SupplierTBL sup on rms.Supplier = sup.supplierID
join T_Hierarchys h ON apb.t_hierarchy_id = h.id
LEFT JOIN WasteCatTBL w ON w.ID = wpp.WasteGroup
LEFT JOIN Oti_lagertempTBL sto ON p.lagertemp = sto.LagerTempID
WHERE p.Aktiv = 1
AND ISNULL(rms.Supplier,0) = CASE
WHEN ISNULL(@p1,0) >0 THEN @p1
ELSE ISNULL(rms.Supplier,0)
END
| Lists chemical with classification, form, storage and garbagehandling info for one location. | Delete | Edit |
| 132 | Peroxide classification, one hierarchy | ReportsPage | 104 | AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT
Product = prod.Produktnavn,
Substance = s.[Navn Systematisk],
Amount = ps.Amount,
PeroxideClass = p.PeroxideClass,
Cas = s.[CAS nr],
prod.[Dessin nr] as ProductNumber
FROM PeroxideTBL p
JOIN SubstansTabel s
ON p.CAS = s.[CAS nr]
JOIN ProduktSubstansTabel ps
ON s.SubstansID = ps.SubstansID
JOIN ProduktTabel prod
ON ps.ProduktID = prod.ProduktID
JOIN APB_Brugssted_RM_TBL apb
ON apb.t_hierarchy_id = @p0 AND prod.ProduktID = apb.ProduktID
JOIN T_Hierarchys h
ON apb.t_hierarchy_id = h.id
WHERE ps.Amount > 95
| List containing all, in the database, active chemicals containing substances classified as peroxides (Included in Peroxide table), for a location. | Delete | Edit |
| 167 | ABC Product-Substances related, one hierarchy | ReportsPage | 105 | | using System; public class $$ContainerClassName$$ { $$FieldDef$$ } | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | /*
Created 23-03-2020
ABC Råvarer-Substanser-Tilknyttede for 1 brugssted
*/
DECLARE @ExcludeHirList TABLE (id INT)
insert into @ExcludeHirList select Id from HierarchyPaths() where Path like '%0 Undervisning%' or Path like '%Chymeia Hovedkontor%'
select
pt.ProduktID as RåvareID,
pt.ProduktNavn as Råvarenavn,
am.Anvendelsesområde,
st.[navn systematisk] as Substans,
isnull(pst.amount,0) as Koncentration_af_substans_i_råvare,
st.SubstansID as SubstansId,
ISNULL(st.[CAS nr],'') as CAS_nr,
case isnull(st.InternalCode,'')
when 'A' then 'A'
when 'B' then 'B'
when 'C' then 'C'
when 'D' then 'D'
else 'Ikke Vurderet' end as A_B_eller_C_stof,
addr.[address] as Adresse,
isnull(pt.recipient,'') as Recipient_AO_ADM,
cat.category_value as Udledningsvej,
ISNULL(wct.AffaldDK,'') as Affaldsgruppe
,SUP.SupplierName as [Leverandør],
pt.[Dessin nr] as ProductNumber
from
Substanstabel st
left outer join produktsubstanstabel pst on pst.substansid = st.substansid
inner join ProduktTabel pt on pt.produktid = pst.ProduktID
inner join Anvendelsesområde_RM am on am.AnvendelsesområdeID = pt.AnvendelsesområdeID
inner join APB_Brugssted_RM_TBL abr on abr.ProduktID = pt.produktid
inner join T_Hierarchys hir on hir.id = abr.t_hierarchy_id
inner join T_Hierarchy_Informations hirInfo on hirInfo.t_hierarchy_id = hir.id
inner join T_Addresses addr on addr.id = hirInfo.t_address_id
inner join T_Categories cat on cat.id = abr.category_recipient_id
left outer join WasteCatTBL wct on wct.ID = pt.Affaldsgruppe
left outer join RM_supplier RM_SUP on RM_SUP.ProduktID = pt.ProduktID
left outer join SupplierTBL SUP on SUP.supplierID = RM_SUP.Supplier
where
not hir.id in (select id from @ExcludeHirList) and pt.Aktiv = 1 and hir.id = @p0
order by pt.ProduktNavn | ABC: Råvarer-Substanser-Tilknyttede for 1 brugssted | Delete | Edit |
| 125 | SVHC report, one hierarchy | ReportsPage | 106 | | using System;
public class $$ContainerClassName$$
{
$$FieldDef$$
}
| System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT
Substance = s.[Navn Systematisk],
RmoaConclusion = pact.[RMOA Conclusion],
PactInclusionDate = pact.[Inclusion in PACT],
Scope = pact.Scope,
Authority = pact.Authority,
IndexNumber = s.Indeksnummer,
Cas = s.[CAS nr],
EfNumber = s.[EF nummer]
FROM SubstansTabel s
JOIN ProduktSubstansTabel ps
ON s.SubstansID = ps.SubstansID
JOIN APB_Brugssted_RM_TBL apb
ON apb.t_hierarchy_id = @p0 AND ps.ProduktID = apb.ProduktID
JOIN T_Hierarchys h
ON apb.t_hierarchy_id = h.id
JOIN PACT_TBL pact
ON s.[CAS nr] = pact.[CAS Number]
| List containing all, in the database, active chemicals with problematic substances (Included in PACT table) for a location. | Delete | Edit |
| 105 | Environmental Hazard Low Risk, one hierarchy element | ReportsPage | 107 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT Product = p.Produktnavn, ProductClassification = p.klassificering from ProduktTabel p join APB_Brugssted_RM_TBL apb on apb.t_hierarchy_id = @p0 AND p.ProduktID = apb.ProduktID join T_Hierarchys h on apb.t_hierarchy_id = h.id
where p.klassificering LIKE '%H413%'
AND NOT(p.klassificering LIKE '%H400%'
OR p.klassificering LIKE '%H410%'
OR p.klassificering LIKE '%H411%'
OR p.klassificering LIKE '%H412%') | Lists all, in the database, active chemicals containing substances with low environmental risk, for a location. | Delete | Edit |
| 107 | Environmental Hazard Medium Risk, one hierarchy element | ReportsPage | 108 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT Product = p.Produktnavn, ProductClassification = p.klassificering from ProduktTabel p join APB_Brugssted_RM_TBL apb on apb.t_hierarchy_id = @p0 AND p.ProduktID = apb.ProduktID join T_Hierarchys h on apb.t_hierarchy_id = h.id where p.klassificering LIKE '%H412%'
and not (p.klassificering LIKE '%H400%'
OR p.klassificering LIKE '%H410%'
OR p.klassificering LIKE '%H411%'
OR p.klassificering LIKE '%H413%'); | Lists all, in the database, active chemicals containing substances with medium environmental risk, for a location. | Delete | Edit |
| 165 | ABC Råvarer-Substanser-Tilknyttede for 1 brugssted | ReportsPage | 108 | | using System; public class $$ContainerClassName$$ { $$FieldDef$$ } | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | /*
ABC-Substances added to a hierarchy, for one hierarchy.
Created 23-03-2020
*/
select
pt.ProduktID as RåvareID,
pt.ProduktNavn as Råvarenavn,
am.Anvendelsesområde,
st.[navn systematisk] as Substans,
isnull(pst.amount,0) as Koncentration_af_substans_i_råvare,
st.SubstansID as SubstansId,
ISNULL(st.[CAS nr],'') as CAS_nr,
case isnull(st.InternalCode,'')
when 'A' then 'A'
when 'B' then 'B'
when 'C' then 'C'
when 'D' then 'D'
else 'Ikke Vurderet' end as A_B_eller_C_stof,
(select Path from HierarchyPaths() where Id = hir.id) as Brugssted,
addr.[address] as Adresse,
isnull(pt.recipient,'') as Recipient_AO_ADM,
cat.category_value as Udledningsvej,
ISNULL(wct.AffaldDK,'') as Affaldsgruppe
,SUP.SupplierName as [Leverandør]
,pt.[Dessin nr] as ProductNumber
from
Substanstabel st
left outer join produktsubstanstabel pst on pst.substansid = st.substansid
inner join ProduktTabel pt on pt.produktid = pst.ProduktID
inner join Anvendelsesområde_RM am on am.AnvendelsesområdeID = pt.AnvendelsesområdeID
inner join APB_Brugssted_RM_TBL abr on abr.ProduktID = pt.produktid
inner join T_Hierarchys hir on hir.id = abr.t_hierarchy_id
inner join T_Hierarchy_Informations hirInfo on hirInfo.t_hierarchy_id = hir.id
inner join T_Addresses addr on addr.id = hirInfo.t_address_id
inner join T_Categories cat on cat.id = abr.category_recipient_id
left outer join WasteCatTBL wct on wct.ID = pt.Affaldsgruppe
left outer join RM_supplier RM_SUP on RM_SUP.ProduktID = pt.ProduktID
left outer join SupplierTBL SUP on SUP.supplierID = RM_SUP.Supplier
where
hir.id = @p0 and pt.Aktiv = 1
order by pt.ProduktNavn | ABC: Råvarer-Substanser-Tilknyttede for 1 brugssted | Delete | Edit |
| 109 | Environmental Hazard High Risk, one hierarchy element | ReportsPage | 109 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT Product = p.Produktnavn,
ProductClassification = p.klassificering
FROM ProduktTabel p
JOIN APB_Brugssted_RM_TBL apb ON apb.t_hierarchy_id = @p0
AND p.ProduktID = apb.ProduktID
JOIN T_Hierarchys h ON apb.t_hierarchy_id = h.id
WHERE p.klassificering LIKE '%H410%'
OR p.klassificering LIKE '%H400%'
OR p.klassificering LIKE '%H411%'
OR p.klassificering LIKE '%EUH059%'; | Lists all, in the database, active chemicals containing substances with high environmental risk, for a location. | Delete | Edit |
| 229 | Chemical Report | ReportsPage | 110 | AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | declare @aoLang as varchar(2) = case lower(@p2) when 'da-dk' then 'dk' else 'en' end;
DECLARE @risktable TABLE
(produktid int PRIMARY KEY)
insert into @risktable
select distinct p.produktid from ProduktTabel p
inner join ProduktSubstansTabel ps on ps.ProduktID = p.ProduktID
inner join SubstansTabel sub on sub.SubstansID = ps.SubstansID
and (
(
p.klassificering like '%H300%'
OR p.klassificering like '%H301%'
OR p.klassificering like '%H310%'
OR p.klassificering like '%H311%'
OR p.klassificering like '%H330%'
OR p.klassificering like '%H331%'
OR p.klassificering like '%H370%'
OR p.klassificering like '%H350%'
OR p.klassificering like '%H360%'
OR p.klassificering like '%H340%'
OR p.klassificering like '%H317%'
OR p.klassificering like '%H334%'
)
OR
(
(
sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%'
)
AND
(
((sub.[DK Kraeftlisten] = 1 and ps.Amount >= 0.1) OR p.klassificering LIKE '%CARC. 1%' OR p.klassificering like '%muta%')
OR ((sub.[DK Kraeftlisten] = 1 and ps.Amount >= 1.0) OR p.klassificering LIKE '%CARC. 2%')
)
)
);
SELECT DISTINCT
Hierarchy = ppp.[Path],
Product = p.Produktnavn,
ProductClassification = isnull(CONCAT(isnull(p.klassificering,''),' ', dbo.GetPatternMatches('%EUH[0-9][0-9][0-9]%',et.Andenmaerkning_CLP,6, ' ')),'-'),
SupplierName = ISNULL(s.SupplierName,''),
case lower(@p2)
when 'en-us' then a.AnvendelsesområdeEN
else a.Anvendelsesområde
end as Usage,
CASE
WHEN sds.v30 IS NULL
OR sds.v30 LIKE 'Ikke anvendelig%'
OR sds.v30 LIKE '-'
OR sds.v30 LIKE 'Not applicable%'
OR sds.v30 LIKE 'EUH210'
OR sds.v30 LIKE '-¤'
OR sds.v30 LIKE '¤'
OR sds.v30 LIKE 'Ingen¤%'
THEN ''
ELSE REPLACE(REPLACE(sds.v30, 'Sikkerhedsdatablad kan på anmodning rekvireres. (EUH210)', ''), '¤', '')
END AS OtherRemark,
(STUFF((SELECT (', ' + sub.[CAS nr])
FROM ProduktSubstansTabel prs
inner join SubstansTabel sub on sub.SubstansID = prs.SubstansID
WHERE
prs.ProduktID = p.ProduktID
FOR XML PATH('')), 1, 2, '')) AS CAS_nr,
' ' + (select top 1 Item from GHSStringToImageList(et.piktogram_CLP,'24','background-color:white; padding:4px;border:solid 1px #c0c0c0;margin:1px;')) + ' ' as picto_noexport_imgsrc,
et.piktogram_CLP as GHSNumbers,
p.[Fysisk tilstand] as Tilstandsform,
(STUFF((SELECT ('' + 'SDS')
from T_Files where id in (select top 1 fileid from T_ProductFiles where ProductId = p.produktid and not Deprecated = 1 and US = 0 order by CreatedDate desc)
FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'),1, 0, N'')) as SDSLinks_noexport,
apbrm.Departments
,p.[Dessin nr] as ProductNumber ,
cast((select case when exists(select produktid from @risktable where produktid = p.ProduktID) then 1 else 0 end) as bit) as IsAtRisk
FROM ProduktTabel p
JOIN Anvendelsesområde_RM a ON a.AnvendelsesområdeID = p.AnvendelsesområdeID
LEFT JOIN RM_supplier rms ON p.produktid = rms.produktid
LEFT JOIN suppliertbl s ON rms.supplier = s.supplierid
LEFT JOIN SDSVersionTbl AS sds ON p.ProduktID = sds.v98 and sds.Released = 1 and lower(sds.v99) = @aoLang and lower(sds.type) = '3_clp'
JOIN APB_Brugssted_RM_TBL apbrm ON apbrm.ProduktID = p.ProduktID
JOIN T_Hierarchys h ON h.id = apbrm.t_hierarchy_id
LEFT JOIN EtiketTBL et on et.ProduktID = p.ProduktID and lower(sprog) = @aoLang
JOIN dbo.HierarchyPaths() ppp ON ppp.Id = apbrm.t_hierarchy_id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
WHERE ISNULL(rms.Supplier, 0) = CASE
WHEN ISNULL(@p1, 0) > 0
THEN @p1
ELSE ISNULL(rms.Supplier, 0)
END | Lists all chemicals on a location and all its sub locations. | Delete | Edit |
| 222 | Chemical Report APV Requirement | ReportsPage | 110 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
Hierarchy = ppp.[Path],
Product = p.Produktnavn,
('Instruktion') AS DocLink_noexport,
ProductClassification = ISNULL(p.klassificering,'-'),
SupplierName = ISNULL(s.SupplierName,''),
Usage = a.Anvendelsesområde,
(STUFF((SELECT (', ' + sub.[CAS nr])
FROM ProduktSubstansTabel prs
inner join SubstansTabel sub on sub.SubstansID = prs.SubstansID
WHERE
prs.ProduktID = p.ProduktID
FOR XML PATH('')), 1, 2, '')) AS CAS_nr,
' ' + (select top 1 Item from GHSStringToImageList(et.piktogram_CLP,'24','background-color:white; padding:4px;border:solid 1px #c0c0c0;margin:1px;')) + ' ' as picto_noexport_imgsrc,
p.[Fysisk tilstand] as Tilstandsform,
(STUFF((SELECT ('' + 'SDS')
from T_Files where id in (select top 1 fileid from T_ProductFiles where ProductId = p.produktid and not Deprecated = 1 and US = 0 order by CreatedDate desc)
FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'),1, 0, N'')) as SDSLinks_noexport,
isnull(es.APV_ES_navn,'') as es,
p.[Dessin nr] as ProductNumber
FROM ProduktTabel p
JOIN Anvendelsesområde_RM a ON a.AnvendelsesområdeID = p.AnvendelsesområdeID
LEFT JOIN RM_supplier rms ON p.produktid = rms.produktid
JOIN suppliertbl s ON rms.supplier = s.supplierid
JOIN APB_Brugssted_RM_TBL apbrm ON apbrm.ProduktID = p.ProduktID
JOIN T_Hierarchys h ON h.id = apbrm.t_hierarchy_id
LEFT JOIN EtiketTBL et on et.ProduktID = p.ProduktID and sprog = 'DK'
JOIN dbo.HierarchyPaths() ppp ON ppp.Id = apbrm.t_hierarchy_id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
LEFT OUTER JOIN ProduktSubstansTabel ps ON p.ProduktID = ps.ProduktID
LEFT OUTER JOIN SubstansTabel sub ON ps.SubstansID = sub.SubstansID
left outer join APV_anvendelseTBL es on es.APV_anvendelse_ID = apbrm.AnvendelseES_ID
WHERE rms.Supplier = CASE
WHEN ISNULL(@p1,0) >0 THEN @p1
ELSE rms.Supplier
END
AND
(
(
p.klassificering like '%H300%'
OR p.klassificering like '%H301%'
OR p.klassificering like '%H310%'
OR p.klassificering like '%H311%'
OR p.klassificering like '%H330%'
OR p.klassificering like '%H331%'
OR p.klassificering like '%H370%'
OR p.klassificering like '%H350%'
OR p.klassificering like '%H360%'
OR p.klassificering like '%H340%'
OR p.klassificering like '%H317%'
OR p.klassificering like '%H334%'
)
OR
(
(
sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%'
)
AND
(
(((sub.[DK Kraeftlisten] = 1 and ps.Amount >= 0.1) OR p.klassificering LIKE '%CARC. 1%' OR p.klassificering like '%muta%'))
OR
(sub.[DK Kraeftlisten] <> 1 and p.klassificering LIKE '%CARC. 2%' and ps.Amount >= 1.0)
)
)
)
| Lists all chemicals on a location and all its sub locations. | Delete | Edit |
| 206 | Focus report | ReportsPage | 120 | AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT [Path],
Product,
SupplierName,
SubstanceOnCancerList = CAST(MAX(a.SubstanceOnCancerList) AS BIT),
SubstanceAllergy = CAST(MAX(a.SubstanceAllergy) AS BIT),
SubstanceOnPregnantList = CAST(MAX(a.SubstanceOnPregnantList) AS BIT),
ProductToxic = CAST(MAX(a.ProductToxic) AS BIT),
Usage,
ProductNumber
FROM (
SELECT DISTINCT [Path] = ppp.[Path],
Product = p.Produktnavn,
FatherId = p.ProduktID,
ProductToxic = CASE
WHEN (
(
p.klassificering LIKE '%H300%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H310%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H330%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H301%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H301%' or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H311%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H311%' or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H331%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H331%' or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H370%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H370%')
)
OR (
p.klassificering LIKE '%H350%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H350%')
)
OR (
p.klassificering LIKE '%H360%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H360%')
)
OR (
p.klassificering LIKE '%H340%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H340%')
)
)
THEN 1
ELSE 0
END,
SubstanceOnCancerList = CASE
WHEN (
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%'
)
AND (
(
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR p.klassificering LIKE '%CARC%'
OR p.klassificering LIKE '%muta%'
)
THEN 1
ELSE 0
END,
SubstanceAllergy = CASE
WHEN (
(ISNULL(ps.Amount, 0) >= ISNULL(sc.H334, 200))
OR (ISNULL(ps.Amount, 0) >= ISNULL(sc.H317, 200))
)
OR (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H334, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H334, 0))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H317, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H317, 0))
)
)
THEN 1
ELSE 0
END,
SubstanceOnPregnantList = CASE
WHEN (
(
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
(
p.klassificering LIKE '%H341%'
OR p.klassificering LIKE '%H350%'
OR p.klassificering LIKE '%H351%'
OR p.klassificering LIKE '%H360%'
OR p.klassificering LIKE '%H361%'
OR p.klassificering LIKE '%H350i%'
OR p.klassificering LIKE '%H370%'
OR p.klassificering LIKE '%H371%'
OR p.klassificering LIKE '%H372%'
OR p.klassificering LIKE '%H373%'
OR p.klassificering LIKE '%H340%'
OR (
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR sub.[substansklass_GHS] LIKE '%H341%'
OR sub.[substansklass_GHS] LIKE '%H350%'
OR sub.[substansklass_GHS] LIKE '%H351%'
OR sub.[substansklass_GHS] LIKE '%H360%'
OR sub.[substansklass_GHS] LIKE '%H361%'
OR sub.[substansklass_GHS] LIKE '%H370%'
OR sub.[substansklass_GHS] LIKE '%H371%'
OR sub.[substansklass_GHS] LIKE '%H372%'
OR sub.[substansklass_GHS] LIKE '%H373%'
)
OR (
p.klassificering LIKE '%H310%'
OR p.klassificering LIKE '%H311%'
OR p.klassificering LIKE '%H312%'
OR p.klassificering LIKE '%H362%'
OR (
sub.[DK Oploesningsmiddel] = 1
AND ps.Amount >= 1
OR sub.DKGraensevaerdiAnm LIKE '%H%'
)
OR sub.[substansklass_GHS] LIKE '%H310%'
OR sub.[substansklass_GHS] LIKE '%H311%'
OR sub.[substansklass_GHS] LIKE '%H312%'
OR sub.[substansklass_GHS] LIKE '%H362%'
)
OR (
sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'
)
OR (
sub.[CAS nr] IN (
'89997-63-7',
'66603-10-9',
'37353-75-6',
'94-26-8',
'2312-35-8',
'71868-10-5',
'80844-07-1',
'89997-63-7',
'420-04-2',
'64-18-6',
'10028-15-6',
'108-46-3',
'05/09/7446',
'3319-31-1',
'83834-59-7',
'38083-17-9',
'71617-10-2',
'61788-44-1',
'115-86-6',
'10222-01-2',
'130328-19-7',
'75-21-8',
'1222-05-5',
'128-37-0',
'80-54-6',
'1453-58-3',
'77-40-7',
'84-61-7',
'265647-11-8',
'130328-20-0',
'137-26-8',
'35554-44-0',
'53988-10-6',
'61617-00-3',
'121158-58-5',
'80-05-7',
'21850-44-2',
'97416-84-7',
'96-76-4',
'118-56-9',
'13595-25-0',
'36861-47-9',
'78-59-1',
'80-05-7',
'7790-98-9',
'75-09-2',
'68479-98-1',
'7601-89-0',
'106-44-5',
'77-90-7',
'5384-21-4',
'95-14-7',
'80-08-0',
'628-96-6',
'693-21-0',
'108-46-3',
'57583-54-7',
'15087-24-8',
'98-54-4',
'80-46-6',
'17527-29-6',
'2144-53-8',
'6864-37-5',
'98-95-3',
'100-21-0',
'77-94-1',
'79-94-7',
'25068-38-6',
'119-36-8',
'131-57-7',
'288-88-0',
'118-82-1',
'96-69-5',
'80-09-1',
'330-54-1',
'99-76-3',
'94-13-3',
'1634-04-4',
'25013-16-5',
'137-30-4'
)
AND sub.[CAS nr] NOT IN ('64-17-5')
)
OR sub.[DK Oploesningsmiddel] = 1
)
)
THEN 1
ELSE 0
END,
SupplierName = sup.SupplierName,
Usage = a.Anvendelsesområde,
p.[Dessin nr] as ProductNumber
FROM APB_Brugssted_RM_TBL apb
INNER JOIN ProduktTabel p ON apb.ProduktID = p.ProduktID
LEFT JOIN RM_supplier rms ON p.ProduktID = rms.ProduktID
LEFT JOIN SupplierTBL sup ON rms.Supplier = sup.supplierID
INNER JOIN T_Categories c ON apb.category_amount_year_unit_id = c.id
INNER JOIN ProduktSubstansTabel ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel sub ON ps.SubstansID = sub.SubstansID
INNER JOIN KlassifikatiONsTabel_GHS sc ON sub.SubstansID = sc.SubstansID
left outer join KlassifikationsTabel_RM_CLP kc on kc.ProduktID = p.ProduktID
INNER JOIN T_Hierarchys h ON apb.t_hierarchy_id = h.id
INNER JOIN dbo.HierarchyPaths() ppp ON ppp.Id = h.id
AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0, 0) > 0
THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
LEFT JOIN Anvendelsesområde_RM a ON a.AnvendelsesområdeID = p.AnvendelsesområdeID
WHERE p.Aktiv = 1
AND (
ISNULL(rms.Supplier, 0) = CASE
WHEN ISNULL(@p1, 0) > 0
THEN @p1
ELSE ISNULL(rms.Supplier, 0)
END
)
-- Allergy
AND (
(
(
(ISNULL(ps.Amount, 0) >= ISNULL(sc.H334, 200))
OR (ISNULL(ps.Amount, 0) >= ISNULL(sc.H317, 200))
)
OR (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H334, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H334, 0))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H317, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H317, 0))
)
)
)
-- Toxic
OR (
(
p.klassificering LIKE '%H300%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H310%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H330%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H301%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H301%' or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H311%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H311%' or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H331%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H331%' or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H370%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H370%')
)
OR (
p.klassificering LIKE '%H350%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H350%')
)
OR (
p.klassificering LIKE '%H360%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H360%')
)
OR (
p.klassificering LIKE '%H340%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H340%')
)
)
-- Pregnant
OR (
(
p.klassificering LIKE '%H341%'
OR p.klassificering LIKE '%H350%'
OR p.klassificering LIKE '%H351%'
OR p.klassificering LIKE '%H360%'
OR p.klassificering LIKE '%H361%'
OR p.klassificering LIKE '%H350i%'
OR p.klassificering LIKE '%H370%'
OR p.klassificering LIKE '%H371%'
OR p.klassificering LIKE '%H372%'
OR p.klassificering LIKE '%H373%'
OR p.klassificering LIKE '%H340%'
OR (
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR sub.[substansklass_GHS] LIKE '%H341%'
OR sub.[substansklass_GHS] LIKE '%H350%'
OR sub.[substansklass_GHS] LIKE '%H351%'
OR sub.[substansklass_GHS] LIKE '%H360%'
OR sub.[substansklass_GHS] LIKE '%H361%'
OR sub.[substansklass_GHS] LIKE '%H370%'
OR sub.[substansklass_GHS] LIKE '%H371%'
OR sub.[substansklass_GHS] LIKE '%H372%'
OR sub.[substansklass_GHS] LIKE '%H373%'
)
OR (
p.klassificering LIKE '%H310%'
OR p.klassificering LIKE '%H311%'
OR p.klassificering LIKE '%H312%'
OR p.klassificering LIKE '%H362%'
OR (
sub.[DK Oploesningsmiddel] = 1
AND ps.Amount >= 1
OR sub.DKGraensevaerdiAnm LIKE '%H%'
)
OR sub.[substansklass_GHS] LIKE '%H310%'
OR sub.[substansklass_GHS] LIKE '%H311%'
OR sub.[substansklass_GHS] LIKE '%H312%'
OR sub.[substansklass_GHS] LIKE '%H362%'
)
OR (
sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'
)
OR (
sub.[CAS nr] IN (
'89997-63-7',
'66603-10-9',
'37353-75-6',
'94-26-8',
'2312-35-8',
'71868-10-5',
'80844-07-1',
'89997-63-7',
'420-04-2',
'64-18-6',
'10028-15-6',
'108-46-3',
'05/09/7446',
'3319-31-1',
'83834-59-7',
'38083-17-9',
'71617-10-2',
'61788-44-1',
'115-86-6',
'10222-01-2',
'130328-19-7',
'75-21-8',
'1222-05-5',
'128-37-0',
'80-54-6',
'1453-58-3',
'77-40-7',
'84-61-7',
'265647-11-8',
'130328-20-0',
'137-26-8',
'35554-44-0',
'53988-10-6',
'61617-00-3',
'121158-58-5',
'80-05-7',
'21850-44-2',
'97416-84-7',
'96-76-4',
'118-56-9',
'13595-25-0',
'36861-47-9',
'78-59-1',
'80-05-7',
'7790-98-9',
'75-09-2',
'68479-98-1',
'7601-89-0',
'106-44-5',
'77-90-7',
'5384-21-4',
'95-14-7',
'80-08-0',
'628-96-6',
'693-21-0',
'108-46-3',
'57583-54-7',
'15087-24-8',
'98-54-4',
'80-46-6',
'17527-29-6',
'2144-53-8',
'6864-37-5',
'98-95-3',
'100-21-0',
'77-94-1',
'79-94-7',
'25068-38-6',
'119-36-8',
'131-57-7',
'288-88-0',
'118-82-1',
'96-69-5',
'80-09-1',
'330-54-1',
'99-76-3',
'94-13-3',
'1634-04-4',
'25013-16-5',
'137-30-4'
)
AND sub.[CAS nr] NOT IN ('64-17-5')
)
OR sub.[DK Oploesningsmiddel] = 1
)
-- Cancer
OR (
(
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%'
)
AND ps.Amount >= 0.1
)
)
) a
GROUP BY a.[Path],
a.Product,
a.SupplierName,
a.Usage,
a.ProductNumber | List containing all chemicals that should given special attention (riskfactors R/H or contains dangerous metals such as Hg, Pb or Cd), for a given location and its sub locations.
...
WHERE p.Aktiv = 1
AND (ISNULL(rms.Supplier,0) = CASE
WHEN ISNULL(@p1,0) >0 THEN @p1
ELSE ISNULL(rms.Supplier,0)
END)
-- Allergy
AND ((((ISNULL(ps.Amount,0) >= ISNULL(sc.H334,200)) OR (ISNULL(ps.Amount,0) >= ISNULL(sc.H317,200)))
OR (((ISNULL(sc.H334,0) > 0) AND (ISNULL(ps.Amount,0) >= (ISNULL(sc.H334,1100) / 10)) AND (ISNULL(ps.Amount,0) < ISNULL(sc.H334,0)))
OR ((ISNULL(sc.H317,0) > 0) AND (ISNULL(ps.Amount,0) >= (ISNULL(sc.H317,1100) / 10)) AND (ISNULL(ps.Amount,0) < ISNULL(sc.H317,0)))))
-- Toxic
OR ((p.klassificering LIKE '%H300%' AND sub.Substansklass_GHS LIKE '%H300%')
OR (p.klassificering LIKE '%H310%' AND sub.Substansklass_GHS LIKE '%H310%')
OR (p.klassificering LIKE '%H330%' AND sub.Substansklass_GHS LIKE '%H330%')
OR (p.klassificering LIKE '%H301%' AND sub.Substansklass_GHS LIKE '%H301%')
OR (p.klassificering LIKE '%H311%' AND sub.Substansklass_GHS LIKE '%H311%')
OR (p.klassificering LIKE '%H331%' AND sub.Substansklass_GHS LIKE '%H331%')
OR (p.klassificering LIKE '%H370%' AND sub.Substansklass_GHS LIKE '%H370%')
OR (p.klassificering LIKE '%H350%' AND sub.Substansklass_GHS LIKE '%H350%')
OR (p.klassificering LIKE '%H360%' AND sub.Substansklass_GHS LIKE '%H360%')
OR (p.klassificering LIKE '%H340%' AND sub.Substansklass_GHS LIKE '%H340%')
OR (p.klassificering LIKE '%R26%' AND sub.Substansklass LIKE '%R26%')
OR (p.klassificering LIKE '%R27%' AND sub.Substansklass LIKE '%R27%')
OR (p.klassificering LIKE '%R28%' AND sub.Substansklass LIKE '%R28%')
OR (p.klassificering LIKE '%R39%' AND sub.Substansklass LIKE '%R39%')
OR (p.klassificering LIKE '%R23%' AND sub.Substansklass LIKE '%R23%')
OR (p.klassificering LIKE '%R24%' AND sub.Substansklass LIKE '%R24%')
OR (p.klassificering LIKE '%R25%' AND sub.Substansklass LIKE '%R25%')
OR (p.klassificering LIKE '%R45%' AND sub.Substansklass LIKE '%R45%')
OR (p.klassificering LIKE '%R49%' AND sub.Substansklass LIKE '%R49%')
OR (p.klassificering LIKE '%R46%' AND sub.Substansklass LIKE '%R46%')
OR (p.klassificering LIKE '%R60%' AND sub.Substansklass LIKE '%R60%')
OR (p.klassificering LIKE '%R61%' AND sub.Substansklass LIKE '%R61%'))
-- Pregnant
OR ((sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub,'') = '')
AND (sub.Substansklass_GHS LIKE '%H340%'
OR sub.Substansklass_GHS LIKE '%H341%'
OR sub.Substansklass_GHS LIKE '%H350%'
OR sub.Substansklass_GHS LIKE '%H351%'
OR sub.Substansklass_GHS LIKE '%H360%'
OR sub.Substansklass_GHS LIKE '%H361%'
OR sub.Substansklass_GHS LIKE '%H370%'
OR sub.Substansklass_GHS LIKE '%H371%'
OR sub.Substansklass_GHS LIKE '%H372%'
OR sub.Substansklass_GHS LIKE '%H373%'
OR sub.Substansklass_GHS LIKE '%H312%'
OR sub.Substansklass LIKE '%R39%'
OR sub.Substansklass LIKE '%R40%'
OR sub.Substansklass LIKE '%R45%'
OR sub.Substansklass LIKE '%R46%'
OR sub.Substansklass LIKE '%R48%'
OR sub.Substansklass LIKE '%R49%'
OR sub.Substansklass LIKE '%R60%'
OR sub.Substansklass LIKE '%R61%'
OR sub.Substansklass LIKE '%R62%'
OR sub.Substansklass LIKE '%R63%'
OR sub.Substansklass LIKE '%R64%'
OR sub.Substansklass LIKE '%R68%'
OR sub.[DK Kraeftlisten] = 1
OR (sub.[DK Oploesningsmiddel] = 1
AND sub.DKGraensevaerdiAnm LIKE '%H%'
AND ps.Amount >= 1)
OR sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'))
-- Cancer
OR ((sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub,'') = '')
AND (sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%'))
| Delete | Edit |
| 161 | Allergy report | ReportsPage | 140 | AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT Hierarchy = ppp.[Path],
s.[CAS nr] as SubstansCas,
Product = p.Produktnavn,
SubstanceAllergy = s.[Navn Systematisk],
SubstanceConcentrationInterval = CASE
WHEN ps.Interval = '0'
THEN CASE
WHEN (
ps.Amount > 0
AND ps.Amount < 0.001
)
THEN '<0,001'
WHEN (
ps.Amount >= 0.001
AND ps.Amount < 0.01
)
THEN '<0,01'
WHEN (
ps.Amount >= 0.01
AND ps.Amount < 0.1
)
THEN '<0,1'
WHEN (
ps.Amount >= 0.1
AND ps.Amount < 1
)
THEN '<1'
WHEN (
ps.Amount >= 1
AND ps.Amount < 5
)
THEN '1-5'
WHEN (
ps.Amount >= 5
AND ps.Amount < 15
)
THEN '5-15'
WHEN (
ps.Amount >= 15
AND ps.Amount < 25
)
THEN '15-25'
WHEN (
ps.Amount >= 25
AND ps.Amount < 40
)
THEN '25-40'
WHEN (
ps.Amount >= 40
AND ps.Amount < 60
)
THEN '40-60'
WHEN (
ps.Amount >= 60
AND ps.Amount < 80
)
THEN '60-80'
WHEN (
ps.Amount >= 80
AND ps.Amount < 95
)
THEN '80-95'
WHEN (
ps.Amount >= 95
AND ps.Amount <= 100
)
THEN '95-100'
ELSE ps.Interval
END
ELSE ps.Interval
END,
ProductAllergy = IIF((
CASE
WHEN ((ISNULL(ps.Amount, 0) >= ISNULL(sc.H317, 200)))
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END
) = 1, 'X', ''),
ProductRespAllergy = IIF((
CASE
WHEN ((ISNULL(ps.Amount, 0) >= ISNULL(sc.H334, 200)))
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END
) = 1, 'X', ''),
ProductAllergyReaction = IIF((
CASE
-- when ProductAllergy is true, return 0 for this column
WHEN (
(ISNULL(ps.Amount, 0) >= ISNULL(sc.H334, 200))
OR (ISNULL(ps.Amount, 0) >= ISNULL(sc.H317, 200))
)
THEN CAST(0 AS BIT)
WHEN (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H334, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H334, 0))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H317, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H317, 0))
)
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END
) = 1, 'X', ''),
SubstanceAllergyProductAllergy = IIF((
CASE
-- if ProductAllergyReaction is true, return 0 in this column
WHEN (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H334, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H334, 0))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H317, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H317, 0))
)
)
THEN CAST(0 AS BIT)
WHEN (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) < (ISNULL(sc.H334, 0) / 10))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) < (ISNULL(sc.H317, 0) / 10))
)
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END
) = 1, 'X', '')
,p.[Dessin nr] as ProductNumber
FROM APB_Brugssted_RM_TBL apb
INNER JOIN ProduktTabel p ON apb.ProduktID = p.ProduktID
INNER JOIN T_Categories c ON apb.category_amount_year_unit_id = c.id
INNER JOIN ProduktSubstansTabel ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel s ON ps.SubstansID = s.SubstansID
INNER JOIN T_Hierarchys h ON apb.t_hierarchy_id = h.id
INNER JOIN KlassifikatiONsTabel_GHS sc ON s.SubstansID = sc.SubstansID
INNER JOIN dbo.HierarchyPaths() ppp ON ppp.Id = h.id
AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0, 0) > 0
THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
WHERE p.Aktiv = 1
AND (
(ISNULL(ps.Amount, 0) >= ISNULL(sc.H334, 200))
OR (ISNULL(ps.Amount, 0) >= ISNULL(sc.H317, 200))
)
OR (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H334, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H334, 0))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H317, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H317, 0))
)
)
OR (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) < (ISNULL(sc.H334, 0) / 10))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) < (ISNULL(sc.H317, 0) / 10))
)
)
| List containing all, in the database, active chemicals, with allergenic properties for a location and its sub locations....WHERE p.Aktiv = 1AND ((ps.Amount >= sc.H334) OR (ps.Amount >= sc.H317)OR ((ps.Amount < sc.H334) AND (sc.H334 > 0) AND (ps.Amount >= (sc.H334 / 10))) OR ((ps.Amount < sc.H317) AND (sc.H317 > 0) AND (ps.Amount >= (sc.H317 / 10)))OR ((sc.H334 > 0) OR (ps.Amount < (sc.H317 / 10)) AND (sc.H317 > 0)) AND (ps.Amount < (sc.H334 / 10))) | Delete | Edit |
| 218 | Fire report | ReportsPage | 150 | AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
Hierarchy = ppp.[Path],
Product = p.Produktnavn,
MixedWithWater = CAST(ISNULL(p.BlandbaH2O,0) AS BIT),
FlameValue = CAST(p.Flammepunkt AS INT),
FlameClass = CASE
WHEN (p.[Fysisk tilstand] <> 'Flydende' and p.[Fysisk tilstand] <> 'Gel') THEN '-'
WHEN p.Flammepunkt < 23 THEN 'I - '
WHEN p.Flammepunkt >= 23 AND p.Flammepunkt <= 60 THEN 'II -'
WHEN p.Flammepunkt > 60 AND p.Flammepunkt <= 93 THEN 'III -'
ELSE '-'
END +
CASE
WHEN (p.[Fysisk tilstand] <> 'Flydende' and p.[Fysisk tilstand] <> 'Gel') THEN ''
WHEN ISNULL(p.BlandbaH2O,0) = 1 THEN '2'
ELSE '1'
END
,p.[Dessin nr] as ProductNumber
from APB_Brugssted_RM_TBL apb
join ProduktTabel p
on apb.ProduktID = p.ProduktID
join T_Hierarchys h on apb.t_hierarchy_id = h.id
JOIN dbo.HierarchyPaths() ppp
ON ppp.Id = h.id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
WHERE p.Aktiv = 1
AND p.Flammepunkt IS NOT null
AND p.Flammepunkt < 99
AND (p.klassificering LIKE '%H220%'
OR p.klassificering LIKE '%H221%'
OR p.klassificering LIKE '%H222%'
OR p.klassificering LIKE '%H223%'
OR p.klassificering LIKE '%H224%'
OR p.klassificering LIKE '%H225%'
OR p.klassificering LIKE '%H226%'
OR p.klassificering LIKE '%H228%'
OR p.klassificering LIKE '%H241%'
OR p.klassificering LIKE '%H242%'
OR p.klassificering LIKE '%H250%'
OR p.klassificering LIKE '%H251%'
OR p.klassificering LIKE '%H252%'
OR p.klassificering LIKE '%H260%'
OR p.klassificering LIKE '%H261%'
OR p.klassificering LIKE '%R10%'
OR p.klassificering LIKE '%R11%'
OR p.klassificering LIKE '%R12%')
AND (p.[Fysisk tilstand] like 'Flydende' or p.[Fysisk tilstand] like 'Gel') | List containing all, in the database, active flammable chemicals for a location and its sub locations.
...
WHERE p.Aktiv = 1
AND p.Flammepunkt IS NOT null
AND p.Flammepunkt < 99
AND (p.klassificering LIKE '%H220%'
OR p.klassificering LIKE '%H221%'
OR p.klassificering LIKE '%H222%'
OR p.klassificering LIKE '%H223%'
OR p.klassificering LIKE '%H224%'
OR p.klassificering LIKE '%H225%'
OR p.klassificering LIKE '%H226%'
OR p.klassificering LIKE '%H228%'
OR p.klassificering LIKE '%R10%'
OR p.klassificering LIKE '%R11%'
OR p.klassificering LIKE '%R12%')
AND p.[Fysisk tilstand] like 'Flydende' | Delete | Edit |
| 169 | Handling report | ReportsPage | 160 | AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
p.ProduktId,
Hierarchy = ppp.[Path],
Product = p.Produktnavn + CASE
WHEN ISNULL(anv.APV_ES_navn,'') <> '' THEN ' // ' + anv.APV_ES_navn
ELSE ''
END,
ProductGloves = (select * from GetGlovesPPEList(p.ProduktId, anv.APV_anvendelse_ID)),
Inhalation = (select * from GetInhalationPPEList(p.ProduktId,anv.APV_anvendelse_ID)),
ProductGlasses = (select * from GetGooglesPPEList(p.ProduktId,anv.APV_anvendelse_ID)),
BodyProtection = (select * from GetBodyPPEList(p.ProduktId,anv.APV_anvendelse_ID)),
p.[Dessin nr] as ProductNumber
FROM APB_Brugssted_RM_TBL apb
JOIN ProduktTabel p ON p.ProduktID = apb.ProduktID
JOIN EtiketTBL e ON e.ProduktID = p.ProduktID
--JOIN T_Hierarchys h ON apb.t_hierarchy_id = @p0 AND h.id = apb.t_hierarchy_id
LEFT JOIN ES_Produkt_TBL esprod ON esprod.ProduktID = p.ProduktID
LEFT JOIN APV_anvendelseTBL anv ON anv.APV_anvendelse_ID = esprod.ES_ID
JOIN T_Hierarchys h ON h.id = apb.t_hierarchy_id
JOIN dbo.HierarchyPaths() ppp
ON ppp.Id = h.id AND ppp.PathIDs LIKE CASE
WHEN (IsNull(@p0,0) > 0) THEN '%$' + CAST(@p0 AS NVARCHAR) + '$%'
ELSE '%'
END
WHERE ISNULL(p.Aktiv,0) = 1
order by ppp.[Path], Product | List containing all, in the database, active products handling requirements for a given location and its sub locations. | Delete | Edit |
| 215 | Garbage report | ReportsPage | 170 | AlphaOmegaSuperUsers;AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
Product,
GarbageGroup,
GarbageLabel = CASE WHEN Epoxy > 0 THEN 'Epoxy ' ELSE '' END + CASE WHEN Iso > 0 THEN 'Isocyanat ' ELSE '' END + CASE WHEN Cancer > 0 THEN 'Kræft' ELSE '' END,
WasteHandling,
Hierarchy,
ProductNumber,
UN,
PG,
CLASS,
PSN,
DangerousWaste,
CAS_nr,
Substances,
DangerousGoods
FROM (
SELECT DISTINCT
Product = p.Produktnavn,
GarbageGroup = w.AffaldDK,
FatherId = p.ProduktID,
p.Aktiv,
SUM(CAST(ISNULL(sub.epoxyharpiks,0) AS INT) + CAST(ISNULL(sub.EpoxMon,0) AS INT)) AS Epoxy,
SUM(CAST(ISNULL(sub.IsocyanteMon,0) AS INT) + CAST(ISNULL(sub.PraepolIsocyanate,0) AS INT)) AS Iso,
SUM(CAST(ISNULL(sub.[DK Kraeftlisten],0) AS INT)) AS Cancer,
isnull(wp.DK,'') as WasteHandling,
Hierarchy = ppp.[Path],
p.[Dessin nr] as ProductNumber,
isnull(t.UN, '') AS UN,
isnull(t.Pck_grp, '') AS PG,
isnull(t.FareKlass, '') AS CLASS,
isnull(t.ProperShipppingADR, '') AS PSN,
convert(bit, ISNULL((select top 1 case when v94 like '%'+ (select top 1 PhraseAPB collate Danish_Norwegian_CI_AS from PhraseTbl where PhraseID = 52) + '%' then 1 else 0 end from SDSVersionTbl where v98 = p.ProduktID and Released = 1 and type = '3_CLP' and v99 = 'DK'),0)) as DangerousWaste,
convert(bit, ISNULL((select top 1 case when v28 like '%'+ (select top 1 PhraseAPB collate Danish_Norwegian_CI_AS from PhraseTbl where PhraseID = 159) + '%' then 1 else 0 end from SDSVersionTbl where v98 = p.ProduktID and Released = 1 and type = '3_CLP' and v99 = 'DK'),0)) as DangerousGoods,
(STUFF((SELECT (', ' + sub.[CAS nr])
FROM ProduktSubstansTabel prs
inner join SubstansTabel sub on sub.SubstansID = prs.SubstansID
WHERE
prs.ProduktID = p.ProduktID
FOR XML PATH('')), 1, 2, '')) AS CAS_nr,
(STUFF((SELECT (', ' + sub.[Navn Systematisk] + ':' + prs.Interval)
FROM ProduktSubstansTabel prs
inner join SubstansTabel sub on sub.SubstansID = prs.SubstansID
WHERE
prs.ProduktID = p.ProduktID
FOR XML PATH('')), 1, 2, '')) AS Substances
FROM
APB_Brugssted_RM_TBL apb
JOIN ProduktTabel p ON apb.ProduktID = p.ProduktID
JOIN ProduktSubstansTabel ps ON p.ProduktID = ps.ProduktID
JOIN SubstansTabel sub ON ps.SubstansID = sub.SubstansID
JOIN T_Hierarchys h ON apb.t_hierarchy_id = h.id
JOIN WastePhraseProductTBL wpp ON p.ProduktID = wpp.ProductID
LEFT JOIN WasteCatTBL w ON wpp.WasteGroup = w.ID
left outer join WastePhaseTBL wp on wp.WastePhaseID = wpp.WastePhraseID
LEFT OUTER JOIN Transport_ADR_RID_RM AS t ON t.ProduktID = p.ProduktID
JOIN dbo.HierarchyPaths() ppp ON ppp.Id = h.id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
WHERE
isnull(w.AffaldDK,'-') <> '-'
GROUP BY p.ProduktID, p.Produktnavn, w.AffaldDK, p.Aktiv, wp.DK, ppp.[Path], p.[Dessin nr], t.un, t.Pck_grp, t.FareKlass, t.ProperShipppingADR) a
| List containing all, in the database, active chemicals with garbage handling information for a location and its sub locations.
...
WHERE p.Aktiv = 1
AND (ISNULL(sub.epoxyharpiks,0) = 1
OR ISNULL(sub.EpoxMon,0) = 1
OR ISNULL(sub.IsocyanteMon,0) = 1
OR ISNULL(sub.PraepolIsocyanate,0) = 1
OR sub.[DK Kraeftlisten] = 1
OR (p.Affaldsgruppe IS NOT NULL AND w.AffaldDK <> '-'))
| Delete | Edit |
| 204 | Toxic report | ReportsPage | 170 | AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT Hierarchy = ppp.[Path],
sub.[CAS nr] as SubstansCas,
Product = p.Produktnavn,
SubstanceToxic = case when isnull(kc.Ingen_beregning,0) = 1 then '-' else sub.[Navn Systematisk] end,
MustLockup = CAST(1 AS BIT),
SecurityResponsible = CASE
WHEN (
(
p.klassificering LIKE '%H300%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H310%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H330%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H301%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H301%'or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H311%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H311%' or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H331%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H331%' or sub.Substansklass_GHS LIKE '%H330%')
)
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END,
ReportTheft = CASE
WHEN (
(
p.klassificering LIKE '%H300%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H310%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H330%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H301%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H301%' or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H311%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H311%' or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H331%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H331%' or sub.Substansklass_GHS LIKE '%H330%')
)
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END,
apb.Departments
, p.[Dessin nr] as ProductNumber
FROM APB_Brugssted_RM_TBL apb
INNER JOIN ProduktTabel p ON apb.ProduktID = p.ProduktID
left outer join KlassifikationsTabel_RM_CLP kc on kc.ProduktID = p.ProduktID
INNER JOIN ProduktSubstansTabel ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel sub ON ps.SubstansID = sub.SubstansID
INNER JOIN T_Hierarchys h ON apb.t_hierarchy_id = h.id
INNER JOIN dbo.HierarchyPaths() ppp ON ppp.Id = h.id
AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0, 0) > 0
THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
WHERE p.Aktiv = 1
AND (
(
p.klassificering LIKE '%H300%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H310%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H330%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H301%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H301%' or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H311%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H311%' or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H331%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H331%' or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H370%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H370%')
)
OR (
p.klassificering LIKE '%H350%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H350%')
)
OR (
p.klassificering LIKE '%H360%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H360%')
)
OR (
p.klassificering LIKE '%H340%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H340%')
)
)
| List containing all, in the database, active chemicals with toxic properties for a chosen location.
The list contains chemicals which are danger labelled with one or more of the following H-sentences:
H300, H310, H330, H301, H311, H331, H370, H360, H350, H340 | Delete | Edit |
| 224 | Cancer report | ReportsPage | 180 | AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
Hierarchy = ppp.[Path],
Product = p.Produktnavn,
SubstanceRisk = sub.[Navn Systematisk],
sub.[CAS nr] as SubstansCas,
SubstanceConcentrationInterval = CASE ps.Interval
WHEN '0' THEN
CASE
WHEN (ps.Amount > 0 AND ps.Amount < 0.001) THEN '<0,001'
WHEN (ps.Amount >= 0.001 AND ps.Amount < 0.01) THEN '<0,01'
WHEN (ps.Amount >= 0.01 AND ps.Amount < 0.1) THEN '<0,1'
WHEN (ps.Amount >= 0.1 AND ps.Amount < 1) THEN '<1'
WHEN (ps.Amount >= 1 AND ps.Amount < 5) THEN '1-5'
WHEN (ps.Amount >= 5 AND ps.Amount < 15) THEN '5-15'
WHEN (ps.Amount >= 15 AND ps.Amount < 25) THEN '15-25'
WHEN (ps.Amount >= 25 AND ps.Amount < 40) THEN '25-40'
WHEN (ps.Amount >= 40 AND ps.Amount < 60) THEN '40-60'
WHEN (ps.Amount >= 60 AND ps.Amount < 80) THEN '60-80'
WHEN (ps.Amount >= 80 AND ps.Amount < 95) THEN '80-95'
WHEN (ps.Amount >= 95 AND ps.Amount <= 100) THEN '95-100'
ELSE
ps.Interval
END
ELSE
ps.Interval
END,
SubstanceOnCancerListGeneralDemands = CAST(1 AS BIT),
SpecialDemands = CASE
WHEN sub.CancerDK_17 = 1
OR sub.CancerDK_27 = 1
OR sub.CancerDK_29_32 = 1
OR sub.CancerDK_33 = 1 THEN CAST(1 AS BIT)
ELSE
CAST(0 AS BIT)
END,
apb.Departments
,p.[Dessin nr] as ProductNumber
FROM APB_Brugssted_RM_TBL apb
JOIN ProduktTabel p
ON apb.ProduktID = p.ProduktID
JOIN ProduktSubstansTabel ps
ON p.ProduktID = ps.ProduktID
JOIN SubstansTabel sub
ON ps.SubstansID = sub.SubstansID
JOIN T_Hierarchys h
ON apb.t_hierarchy_id = h.id
JOIN dbo.HierarchyPaths() ppp
ON ppp.Id = h.id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
WHERE p.Aktiv = 1
AND (sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub,'') = '')
AND (sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%')
AND
(
(((sub.[DK Kraeftlisten] = 1 and ps.Amount >= 0.1) OR p.klassificering LIKE '%CARC. 1%' OR p.klassificering like '%muta%'))
OR
(sub.[DK Kraeftlisten] <> 1 and p.klassificering LIKE '%CARC. 2%' and ps.Amount >= 1.0)
) | List containing all, in the database, active chemicals with carcinogenic properties for a single hierarchy. The chemical is on the list, if the chemical is:
- Included by the Statutory order on prevention of the risk of cancer, appendix 1A, regardless danger labelling OR
- Is classified with Muta. 1A: H340 or Muta 1B; H340 OR
- Is classified with on of the following: Carc. 1A; H350 eller H350i, Carc. 1B; H350 eller H350i, Carc. 2; H351 | Delete | Edit |
| 227 | Pregnancy report | ReportsPage | 190 | AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT selection.Hierarchy,
selection.Product,
selection.SubstanceRisk,
selection.SubstanceConcentrationInterval,
longterm,
skincontact,
heavymetals,
endocrinedisruptors,
inhalation,
SubstansCas,
ProductNumber
FROM (
SELECT DISTINCT ppp.[Path] AS Hierarchy,
p.Produktnavn AS Product,
sub.[Navn Systematisk] AS SubstanceRisk,
sub.[substansklass_GHS] AS SubstanceClassification,
sub.[CAS nr] AS SubstansCas,
sub.strukturformel AS SubstansFormel,
sub.[DK Kraeftlisten],
sub.[DK Oploesningsmiddel],
sub.DKGraensevaerdiAnm,
ps.Amount,
CASE ps.Interval
WHEN '0'
THEN CASE
WHEN (
ps.Amount > 0
AND ps.Amount < 0.001
)
THEN '<0,001'
WHEN (
ps.Amount >= 0.001
AND ps.Amount < 0.01
)
THEN '<0,01'
WHEN (
ps.Amount >= 0.01
AND ps.Amount < 0.1
)
THEN '<0,1'
WHEN (
ps.Amount >= 0.1
AND ps.Amount < 1
)
THEN '<1'
WHEN (
ps.Amount >= 1
AND ps.Amount < 5
)
THEN '1-5'
WHEN (
ps.Amount >= 5
AND ps.Amount < 15
)
THEN '5-15'
WHEN (
ps.Amount >= 15
AND ps.Amount < 25
)
THEN '15-25'
WHEN (
ps.Amount >= 25
AND ps.Amount < 40
)
THEN '25-40'
WHEN (
ps.Amount >= 40
AND ps.Amount < 60
)
THEN '40-60'
WHEN (
ps.Amount >= 60
AND ps.Amount < 80
)
THEN '60-80'
WHEN (
ps.Amount >= 80
AND ps.Amount < 95
)
THEN '80-95'
WHEN (
ps.Amount >= 95
AND ps.Amount <= 100
)
THEN '95-100'
ELSE ps.Interval
END
ELSE ps.Interval
END AS SubstanceConcentrationInterval,
CAST(CASE
WHEN (
(
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR sub.[substansklass_GHS] LIKE '%H340%'
OR sub.[substansklass_GHS] LIKE '%H341%'
OR sub.[substansklass_GHS] LIKE '%H350%'
OR sub.[substansklass_GHS] LIKE '%H350i%'
OR sub.[substansklass_GHS] LIKE '%H351%'
OR sub.[substansklass_GHS] LIKE '%H360%'
OR sub.[substansklass_GHS] LIKE '%H361%'
OR sub.[substansklass_GHS] LIKE '%H370%'
OR sub.[substansklass_GHS] LIKE '%H371%'
OR sub.[substansklass_GHS] LIKE '%H372%'
OR sub.[substansklass_GHS] LIKE '%H373%'
OR sub.[substansklass_GHS] LIKE '%EUH440%'
OR sub.[substansklass_GHS] LIKE '%EUH441%'
)
THEN 1
ELSE 0
END AS BIT) AS longterm,
CAST(CASE
WHEN (
(
sub.[DK Oploesningsmiddel] = 1
AND ps.Amount >= 1
OR sub.DKGraensevaerdiAnm LIKE '%H%'
)
OR sub.[substansklass_GHS] LIKE '%H310%'
OR sub.[substansklass_GHS] LIKE '%H311%'
OR sub.[substansklass_GHS] LIKE '%H312%'
OR sub.[substansklass_GHS] LIKE '%H362%'
)
THEN 1
ELSE 0
END AS BIT) AS skincontact,
CAST(CASE
WHEN (
sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'
)
THEN 1
ELSE 0
END AS BIT) AS heavymetals,
CAST(CASE
WHEN (
sub.[CAS nr] IN (
'36861-47-9',
'95342-41-9',
'852541-21-0',
'741687-98-9',
'852541-30-1',
'852541-25-4',
'1782069-81-1',
'27459-10-5',
'77-40-7',
'80-05-7',
'85-68-7',
'117-81-7',
'94-26-8',
'67-97-0',
'84-74-2',
'84-61-7',
'84-69-5',
'4247-02-3',
'8018-01-7',
'74499-35-7',
'210555-94-5',
'27147-75-7',
'121158-58-5',
'57427-55-1'
)
)
THEN 1
ELSE 0
END AS BIT) AS endocrinedisruptors,
CAST(CASE
WHEN (sub.[DK Oploesningsmiddel] = 1)
THEN 1
ELSE 0
END AS BIT) AS inhalation,
p.[Dessin nr] as ProductNumber
FROM APB_Brugssted_RM_TBL AS apb
INNER JOIN ProduktTabel AS p ON apb.ProduktID = p.ProduktID
INNER JOIN ProduktSubstansTabel AS ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel AS sub ON ps.SubstansID = sub.SubstansID
INNER JOIN T_Hierarchys AS h ON apb.t_hierarchy_id = h.id
INNER JOIN dbo.HierarchyPaths() AS ppp ON ppp.Id = h.id
AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0, 0) > 0
THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
WHERE p.[AnvendelsesområdeID] NOT IN (
24,
1040
)
AND p.Aktiv = 1
AND (
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
(
(
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR sub.[substansklass_GHS] LIKE '%H340%'
OR sub.[substansklass_GHS] LIKE '%H341%'
OR sub.[substansklass_GHS] LIKE '%H350%'
OR sub.[substansklass_GHS] LIKE '%H350i%'
OR sub.[substansklass_GHS] LIKE '%H351%'
OR sub.[substansklass_GHS] LIKE '%H360%'
OR sub.[substansklass_GHS] LIKE '%H361%'
OR sub.[substansklass_GHS] LIKE '%H370%'
OR sub.[substansklass_GHS] LIKE '%H371%'
OR sub.[substansklass_GHS] LIKE '%H372%'
OR sub.[substansklass_GHS] LIKE '%H373%'
)
OR (
(
sub.[DK Oploesningsmiddel] = 1
AND ps.Amount >= 1
OR sub.DKGraensevaerdiAnm LIKE '%H%'
)
OR sub.[substansklass_GHS] LIKE '%H310%'
OR sub.[substansklass_GHS] LIKE '%H311%'
OR sub.[substansklass_GHS] LIKE '%H312%'
OR sub.[substansklass_GHS] LIKE '%H362%'
)
OR (
sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'
)
OR (
sub.[substansklass_GHS] LIKE '%EUH380%'
OR sub.[substansklass_GHS] LIKE '%EUH381%'
OR sub.[substansklass_GHS] LIKE '%EUH440%'
OR sub.[substansklass_GHS] LIKE '%EUH441%'
)
OR (
sub.[CAS nr] IN (
'36861-47-9',
'95342-41-9',
'852541-21-0',
'741687-98-9',
'852541-30-1',
'852541-25-4',
'1782069-81-1',
'27459-10-5',
'77-40-7',
'80-05-7',
'85-68-7',
'117-81-7',
'94-26-8',
'67-97-0',
'84-74-2',
'84-61-7',
'84-69-5',
'4247-02-3',
'8018-01-7',
'74499-35-7',
'210555-94-5',
'27147-75-7',
'121158-58-5',
'57427-55-1'
)
)
OR sub.[DK Oploesningsmiddel] = 1
) AND NOT sub.[CAS nr] IN ('64-17-5')
) AS selection; | List containing all chemicals that are known to affect pregnancy/foetus, for a location and its sub locations
...
WHERE p.[AnvendelsesområdeID] NOT IN (
24,
1040
)
AND p.Aktiv = 1
AND (
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
(
p.klassificering LIKE '%H341%'
OR p.klassificering LIKE '%H350%'
OR p.klassificering LIKE '%H351%'
OR p.klassificering LIKE '%H360%'
OR p.klassificering LIKE '%H361%'
OR p.klassificering LIKE '%H350i%'
OR p.klassificering LIKE '%H370%'
OR p.klassificering LIKE '%H371%'
OR p.klassificering LIKE '%H372%'
OR p.klassificering LIKE '%H373%'
OR p.klassificering LIKE '%H340%'
OR (
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR sub.[substansklass_GHS] LIKE '%H340%'
OR sub.[substansklass_GHS] LIKE '%H341%'
OR sub.[substansklass_GHS] LIKE '%H350%'
OR sub.[substansklass_GHS] LIKE '%H350i%'
OR sub.[substansklass_GHS] LIKE '%H351%'
OR sub.[substansklass_GHS] LIKE '%H360%'
OR sub.[substansklass_GHS] LIKE '%H361%'
OR sub.[substansklass_GHS] LIKE '%H370%'
OR sub.[substansklass_GHS] LIKE '%H371%'
OR sub.[substansklass_GHS] LIKE '%H372%'
OR sub.[substansklass_GHS] LIKE '%H373%'
)
OR (
p.klassificering LIKE '%H310%'
OR p.klassificering LIKE '%H311%'
OR p.klassificering LIKE '%H312%'
OR p.klassificering LIKE '%H362%'
OR (
sub.[DK Oploesningsmiddel] = 1
AND ps.Amount >= 1
OR sub.DKGraensevaerdiAnm LIKE '%H%'
)
OR sub.[substansklass_GHS] LIKE '%H310%'
OR sub.[substansklass_GHS] LIKE '%H311%'
OR sub.[substansklass_GHS] LIKE '%H312%'
OR sub.[substansklass_GHS] LIKE '%H362%'
)
OR (
sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'
)
OR (
sub.[CAS nr] IN (
'36861-47-9',
'95342-41-9',
'852541-21-0',
'741687-98-9',
'852541-30-1',
'852541-25-4',
'1782069-81-1',
'27459-10-5',
'77-40-7',
'80-05-7',
'85-68-7',
'117-81-7',
'94-26-8',
'67-97-0',
'84-74-2',
'84-61-7',
'84-69-5',
'4247-02-3',
'8018-01-7',
'74499-35-7',
'210555-94-5',
'27147-75-7',
'121158-58-5',
'57427-55-1'
)
)
OR sub.[DK Oploesningsmiddel] = 1
) AND NOT sub.[CAS nr] IN ('64-17-5') | Delete | Edit |
| 213 | Laboratorie report | ReportsPage | 200 | AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
Hierarchy = ppp.[Path],
Product = p.Produktnavn,
[Synonym] = p.etiketlink,
SupplierName = sup.SupplierName,
ProductClassification = p.klassificering,
ProductPhysicalForm = p.[Fysisk tilstand],
ProductStock = sto.LagertempDK,
GarbageGroup = w.AffaldDK,
Usage = a.Anvendelsesområde,
Room = apb.Lokale,
Cabinet = apb.Skab,
Shelf = apb.Hylde,
Other = apb.Andet
,p.[Dessin nr] as ProductNumber
FROM APB_Brugssted_RM_TBL apb
JOIN dbo.HierarchyPaths() ppp
ON ppp.Id = apb.t_hierarchy_id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
JOIN ProduktTabel p ON apb.ProduktID = p.ProduktID
JOIN WastePhraseProductTBL wpp ON wpp.ProductID = p.ProduktID
JOIN Anvendelsesområde_RM a ON p.AnvendelsesområdeID = a.AnvendelsesområdeID
LEFT JOIN RM_supplier rms ON p.ProduktID = rms.ProduktID
LEFT JOIN SupplierTBL sup on rms.Supplier = sup.supplierID
join T_Hierarchys h ON apb.t_hierarchy_id = h.id
LEFT JOIN WasteCatTBL w ON w.ID = wpp.WasteGroup
LEFT JOIN Oti_lagertempTBL sto ON p.lagertemp = sto.LagerTempID
WHERE p.Aktiv = 1
AND ISNULL(rms.Supplier,0) = CASE
WHEN ISNULL(@p1,0) >0 THEN @p1
ELSE ISNULL(rms.Supplier,0)
END | List containing chemicals with classification, form, storage and garbagehandling info for a given location and its sub locations. | Delete | Edit |
| 131 | Peroxide classification | ReportsPage | 204 | AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT
Hierarchy = ppp.[Path],
Product = prod.Produktnavn,
Substance = s.[Navn Systematisk],
Amount = ps.Amount,
PeroxideClass = p.PeroxideClass,
Cas = s.[CAS nr]
,prod.[Dessin nr] as ProductNumber
FROM PeroxideTBL p
JOIN SubstansTabel s
ON p.CAS = s.[CAS nr]
JOIN ProduktSubstansTabel ps
ON s.SubstansID = ps.SubstansID
JOIN ProduktTabel prod
ON ps.ProduktID = prod.ProduktID
JOIN APB_Brugssted_RM_TBL apb
ON prod.ProduktID = apb.ProduktID
JOIN T_Hierarchys h
ON apb.t_hierarchy_id = h.id
JOIN dbo.HierarchyPaths() ppp
ON ppp.Id = h.id AND ppp.PathIDs LIKE CASE
WHEN (IsNull(@p0,0) > 0) THEN '%$' + CAST(@p0 AS NVARCHAR) + '$%'
ELSE '%'
END
WHERE ps.Amount > 95
| Peroxide classification. Lists all, in the database, active chemicals containing substances with peroxide classification (Included in Peroxide table), for a location and its sub locations. | Delete | Edit |
| 124 | SVHC report | ReportsPage | 206 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT
Hierarchy = ppp.[Path],
Substance = s.[Navn Systematisk],
RmoaConclusion = pact.[RMOA Conclusion],
PactInclusionDate = pact.[Inclusion in PACT],
Scope = pact.Scope,
Authority = pact.Authority,
IndexNumber = s.Indeksnummer,
Cas = s.[CAS nr],
EfNumber = s.[EF nummer]
FROM SubstansTabel s
JOIN ProduktSubstansTabel ps ON s.SubstansID = ps.SubstansID
JOIN APB_Brugssted_RM_TBL apb ON ps.ProduktID = apb.ProduktID
JOIN T_Hierarchys h ON apb.t_hierarchy_id = h.id
JOIN PACT_TBL pact ON s.[CAS nr] = pact.[CAS Number]
JOIN dbo.HierarchyPaths() ppp
ON ppp.Id = h.id AND ppp.PathIDs LIKE CASE
WHEN (IsNull(@p0,0) > 0) THEN '%$' + CAST(@p0 AS NVARCHAR) + '$%'
ELSE '%'
END
| SVHC report. Lists active chemicals containing especially problematic substances (Included in PACT table) for a location and its sub locations. | Delete | Edit |
| 104 | Environmental Hazard Low Risk | ReportsPage | 207 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT Hierarchy = ppp.[Path], Product = p.Produktnavn, ProductClassification = p.klassificering from ProduktTabel p join APB_Brugssted_RM_TBL apb on p.ProduktID = apb.ProduktID join T_Hierarchys h on apb.t_hierarchy_id = h.id JOIN dbo.HierarchyPaths() ppp ON ppp.Id = h.id AND ppp.PathIDs LIKE CASE WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%' ELSE '%' END where p.klassificering LIKE '%H413%' | Lists all, in the database, active chemicals containing substances with low environmental risk, for a location and its sub locations. | Delete | Edit |
| 106 | Environmental Hazard Medium Risk | ReportsPage | 208 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT Hierarchy = ppp.[Path], Product = p.Produktnavn, ProductClassification = p.klassificering from ProduktTabel p join APB_Brugssted_RM_TBL apb on p.ProduktID = apb.ProduktID join T_Hierarchys h on apb.t_hierarchy_id = h.id JOIN dbo.HierarchyPaths() ppp ON ppp.Id = h.id AND ppp.PathIDs LIKE CASE WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%' ELSE '%' END where p.klassificering LIKE '%H412%' | Lists all, in the database, active chemicals containing substances with low environmental risk, for a location and its sub locations. | Delete | Edit |
| 108 | Environmental Hazard High Risk | ReportsPage | 209 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT Hierarchy = ppp.[Path],
Product = p.Produktnavn,
ProductClassification = p.klassificering
FROM ProduktTabel p
JOIN APB_Brugssted_RM_TBL apb ON p.ProduktID = apb.ProduktID
JOIN T_Hierarchys h ON apb.t_hierarchy_id = h.id
JOIN dbo.HierarchyPaths() ppp ON ppp.Id = h.id
AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0, 0) > 0
THEN '%$'+CAST(@p0 AS NVARCHAR(8))+'$%'
ELSE '%'
END
WHERE p.klassificering LIKE '%H410%'
OR p.klassificering LIKE '%H400%'
OR p.klassificering LIKE '%H411%'
OR p.klassificering LIKE '%EUH059%'; | Lists all, in the database, active chemicals containing substances with high environmental risk, for a location and its sub locations. | Delete | Edit |
| 134 | Organisation | ReportsPage | 210 | AlphaOmegaAdmins | using System;
public class $$ContainerClassName$$
{
$$FieldDef$$
}
| System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | CREATE TABLE #Result(
Level0 INT,
Level1 INT,
Level2 INT,
Level3 INT,
Level4 INT,
Level5 INT,
Responsible NVARCHAR(1000),
[Address] NVARCHAR(MAX),
Town NVARCHAR(MAX),
Zip NVARCHAR(10)
)
DECLARE @PlaceOfUse NVARCHAR(1000)
DECLARE @PathIDs NVARCHAR(MAX)
DECLARE @Responsible NVARCHAR(1000)
DECLARE @Address NVARCHAR(MAX)
DECLARE @Town NVARCHAR(MAX)
DECLARE @Zip NVARCHAR(10)
DECLARE @PathPart NVARCHAR(MAX),
@PathIdList NVARCHAR(MAX)
DECLARE #HierCur CURSOR
FOR
SELECT
ppp.PathIDs,
Responsible = hi.responsible_name,
[Address] = a.[address],
Town = a.town,
Zip = a.zipcode
FROM T_Hierarchys h
JOIN dbo.HierarchyPaths() ppp
ON ppp.Id = h.id
AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
JOIN T_Hierarchy_Informations hi on h.id = hi.t_hierarchy_id
LEFT JOIN T_Addresses a
ON a.id = hi.t_address_id
OPEN #HierCur
FETCH NEXT
FROM #HierCur
INTO @PathIDs, @Responsible, @Address, @Town, @Zip
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @StrCount INT = 0,
@PartPos INT = 0,
@PartEndPos INT = 0,
@PartLen INT = 0
DECLARE @Level0 INT = NULL,
@Level1 INT = NULL,
@Level2 INT = NULL,
@Level3 INT = NULL,
@Level4 INT = NULL,
@Level5 INT = NULL
SET @PathIdList = REPLACE(@PathIDs, '$$', ',')
SET @PathIdList = REPLACE(@PathIdList, '$', '')
SET @PartEndPos = CHARINDEX(',', @PathIdList, 0)
IF (@PartEndPos = 0)
SET @Level0 = CAST(@PathIdList AS INT)
ELSE
BEGIN
WHILE (@PartEndPos > 0)
BEGIN
SET @StrCount = @StrCount + 1
SET @PartLen = @PartEndPos - @PartPos
IF (@StrCount = 1) SET @Level0 = CAST(SUBSTRING(@PathIdList, @PartPos, @PartLen) AS INT)
ELSE IF (@StrCount = 2) SET @Level1 = CAST(SUBSTRING(@PathIdList, @PartPos, @PartLen) AS INT)
ELSE IF (@StrCount = 3) SET @Level2 = CAST(SUBSTRING(@PathIdList, @PartPos, @PartLen) AS INT)
ELSE IF (@StrCount = 4) SET @Level3 = CAST(SUBSTRING(@PathIdList, @PartPos, @PartLen) AS INT)
ELSE IF (@StrCount = 5) SET @Level4 = CAST(SUBSTRING(@PathIdList, @PartPos, @PartLen) AS INT)
ELSE IF (@StrCount = 6) SET @Level5 = CAST(SUBSTRING(@PathIdList, @PartPos, @PartLen) AS INT)
SET @PartPos = @PartEndPos+1
SET @PartEndPos = CHARINDEX(',', @PathIdList, @PartPos)
END
SET @StrCount = @StrCount + 1
SET @PartLen = LEN(@PathIdList) + 1 - @PartPos
IF (@StrCount = 1) SET @Level0 = CAST(SUBSTRING(@PathIdList, @PartPos, @PartLen) AS INT)
ELSE IF (@StrCount = 2) SET @Level1 = CAST(SUBSTRING(@PathIdList, @PartPos, @PartLen) AS INT)
ELSE IF (@StrCount = 3) SET @Level2 = CAST(SUBSTRING(@PathIdList, @PartPos, @PartLen) AS INT)
ELSE IF (@StrCount = 4) SET @Level3 = CAST(SUBSTRING(@PathIdList, @PartPos, @PartLen) AS INT)
ELSE IF (@StrCount = 5) SET @Level4 = CAST(SUBSTRING(@PathIdList, @PartPos, @PartLen) AS INT)
ELSE IF (@StrCount = 6) SET @Level5 = CAST(SUBSTRING(@PathIdList, @PartPos, @PartLen) AS INT)
END
INSERT #Result
VALUES(@Level0, @Level1, @Level2, @Level3, @Level4, @Level5, @Responsible, @Address, @Town, @Zip)
FETCH NEXT
FROM #HierCur
INTO @PathIDs, @Responsible, @Address, @Town, @Zip
END
CLOSE #HierCur
DEALLOCATE #HierCur
SELECT
Level0 = h0.hierachy_name,
Level1 = h1.hierachy_name,
Level2 = h2.hierachy_name,
Level3 = h3.hierachy_name,
Level4 = h4.hierachy_name,
Level5 = h5.hierachy_name,
Responsible = r.Responsible,
[Address] = r.[Address],
Town = r.Town,
Zip = r.Zip
FROM #Result r
LEFT JOIN T_Hierarchys h0
ON h0.id = r.Level0
LEFT JOIN T_Hierarchys h1
ON h1.id = r.Level1
LEFT JOIN T_Hierarchys h2
ON h2.id = r.Level2
LEFT JOIN T_Hierarchys h3
ON h3.id = r.Level3
LEFT JOIN T_Hierarchys h4
ON h4.id = r.Level4
LEFT JOIN T_Hierarchys h5
ON h5.id = r.Level5
ORDER BY h0.hierachy_name, h1.hierachy_name, h2.hierachy_name, h3.hierachy_name, h4.hierachy_name, h5.hierachy_name
DROP TABLE #Result
| List of all organisational units included in a given location. | Delete | Edit |
| 157 | Chemical agent | ReportsPage | 220 | AlphaOmegaAdmins | using System;
public class $$ContainerClassName$$
{
$$FieldDef$$
}
| System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
Hierarchy = ppp.[Path],
Responsible = hi.responsible_name,
ResponsibleMail = hi.responsible_mail
FROM T_Hierarchys h
JOIN T_Hierarchy_Informations hi
ON hi.t_hierarchy_id = h.id
JOIN dbo.HierarchyPaths() ppp
ON ppp.Id = h.id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
WHERE ISNULL(hi.responsible_mail,'') <> ''
| Lists all location responsibles for all or a given location and its sub locations. | Delete | Edit |
| 156 | Chemical agents mail address | ReportsPage | 230 | AlphaOmegaAdmins | using System;
public class $$ContainerClassName$$
{
$$FieldDef$$
}
| System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
ResponsibleMail = hi.responsible_mail
FROM T_Hierarchys h
JOIN T_Hierarchy_Informations hi
ON hi.t_hierarchy_id = h.id
JOIN dbo.HierarchyPaths() ppp
ON ppp.Id = h.id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
WHERE ISNULL(hi.responsible_mail,'') <> ''
| Lists mailaddress of all location responsibles for a location and all its sub locations. | Delete | Edit |
| 160 | AO Production | ReportsPage | 240 | AlphaOmegaAdmins | using System;
public class $$ContainerClassName$$
{
$$FieldDef$$
}
| System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
Product = p.Produktnavn,
SupplierName = sup.SupplierName,
SuperUser = sds.v97,
Released = sds.Released,
ReleasedBy = sds.v100,
[Version] = CAST(sds.Version1 AS VARCHAR(5)) + '.' + CAST(sds.version2 AS VARCHAR(5)),
CreatedDate = sds.SDSDato
FROM ProduktTabel p
JOIN SDSVersionTbl sds on p.ProduktID = sds.v98
JOIN RM_supplier rmSup on p.ProduktID = rmSup.ProduktID
JOIN SupplierTBL sup on rmSup.Supplier = sup.supplierID
WHERE sds.[Type] LIKE '%3%' | Report showing the production of documents in AlphaOmega. Lists all products that have been classified and calculated. | Delete | Edit |
| 148 | Empty hierarchy elements | ReportsPage | 250 | AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT
DISTINCT ppp.Path as Hierarchy
FROM
T_Hierarchys h
JOIN T_Hierarchy_Informations hi ON h.id = hi.t_hierarchy_id
JOIN dbo.HierarchyPaths() ppp ON ppp.Id = h.id and ppp.PathIDs LIKE
CASE
WHEN ISNULL(@p0,0) > 0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE
'%'
END
WHERE h.id NOT IN (SELECT DISTINCT t_hierarchy_id FROM APB_Brugssted_RM_TBL)
AND hi.responsible_name IS NOT NULL | Empty hierarchy elements. Lists all locations with no chemicals assigned. | Delete | Edit |
| 135 | Not allocated chemicals | ReportsPage | 260 | AlphaOmegaAdmins | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
p.Produktnavn AS Product,
sup.SupplierName AS SupplierName,
sds.v100 AS ReleasedBy,
sds.v97 AS SuperUser,
sds.SDSDato AS CreatedDate
,p.[Dessin nr] as ProductNumber
FROM ProduktTabel p
JOIN SDSVersionTbl sds
ON p.ProduktID NOT IN (SELECT DISTINCT ProduktID FROM APB_Brugssted_RM_TBL)
AND p.Aktiv = 1
AND sds.Released = 1
AND sds.v98 = p.ProduktID
LEFT JOIN RM_supplier rmSupp
ON rmSupp.ProduktID = p.ProduktID
LEFT JOIN SupplierTBL sup
ON sup.supplierID = rmSupp.Supplier
WHERE ISNULL(rmSupp.Supplier,0) = CASE
WHEN ISNULL(@p0,0) >0 THEN @p0
ELSE ISNULL(rmSupp.Supplier,0)
END
| Not used chemicals. Lists all active in db, released chemicals, currently not assigned to any location. | Delete | Edit |
| 208 | Focus report, non allocated chemicals | ReportsPage | 270 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT [Path],
Product,
SupplierName,
SubstanceOnCancerList = CAST(MAX(a.SubstanceOnCancerList) AS BIT),
SubstanceAllergy = CAST(MAX(a.SubstanceAllergy) AS BIT),
SubstanceOnPregnantList = CAST(MAX(a.SubstanceOnPregnantList) AS BIT),
ProductToxic = CAST(MAX(a.ProductToxic) AS BIT),
Usage,
ProductNumber
FROM (
SELECT DISTINCT [Path] = ppp.[Path],
Product = p.Produktnavn,
FatherId = p.ProduktID,
ProductToxic = CASE
WHEN (
(
p.klassificering LIKE '%H300%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H310%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H330%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H301%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H301%' or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H311%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H311%' or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H331%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H331%' or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H370%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H370%')
)
OR (
p.klassificering LIKE '%H350%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H350%')
)
OR (
p.klassificering LIKE '%H360%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H360%')
)
OR (
p.klassificering LIKE '%H340%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H340%')
)
)
THEN 1
ELSE 0
END,
SubstanceOnCancerList = CASE
WHEN (
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%'
)
AND (
(
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR p.klassificering LIKE '%CARC%'
OR p.klassificering LIKE '%muta%'
)
THEN 1
ELSE 0
END,
SubstanceAllergy = CASE
WHEN (
(ISNULL(ps.Amount, 0) >= ISNULL(sc.H334, 200))
OR (ISNULL(ps.Amount, 0) >= ISNULL(sc.H317, 200))
)
OR (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H334, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H334, 0))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H317, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H317, 0))
)
)
THEN 1
ELSE 0
END,
SubstanceOnPregnantList = CASE
WHEN (
(
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
(
p.klassificering LIKE '%H341%'
OR p.klassificering LIKE '%H350%'
OR p.klassificering LIKE '%H351%'
OR p.klassificering LIKE '%H360%'
OR p.klassificering LIKE '%H361%'
OR p.klassificering LIKE '%H350i%'
OR p.klassificering LIKE '%H370%'
OR p.klassificering LIKE '%H371%'
OR p.klassificering LIKE '%H372%'
OR p.klassificering LIKE '%H373%'
OR p.klassificering LIKE '%H340%'
OR (
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR sub.[substansklass_GHS] LIKE '%H341%'
OR sub.[substansklass_GHS] LIKE '%H350%'
OR sub.[substansklass_GHS] LIKE '%H351%'
OR sub.[substansklass_GHS] LIKE '%H360%'
OR sub.[substansklass_GHS] LIKE '%H361%'
OR sub.[substansklass_GHS] LIKE '%H370%'
OR sub.[substansklass_GHS] LIKE '%H371%'
OR sub.[substansklass_GHS] LIKE '%H372%'
OR sub.[substansklass_GHS] LIKE '%H373%'
)
OR (
p.klassificering LIKE '%H310%'
OR p.klassificering LIKE '%H311%'
OR p.klassificering LIKE '%H312%'
OR p.klassificering LIKE '%H362%'
OR (
sub.[DK Oploesningsmiddel] = 1
AND ps.Amount >= 1
OR sub.DKGraensevaerdiAnm LIKE '%H%'
)
OR sub.[substansklass_GHS] LIKE '%H310%'
OR sub.[substansklass_GHS] LIKE '%H311%'
OR sub.[substansklass_GHS] LIKE '%H312%'
OR sub.[substansklass_GHS] LIKE '%H362%'
)
OR (
sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'
)
OR (
sub.[CAS nr] IN (
'89997-63-7',
'66603-10-9',
'37353-75-6',
'94-26-8',
'2312-35-8',
'71868-10-5',
'80844-07-1',
'89997-63-7',
'420-04-2',
'64-18-6',
'10028-15-6',
'108-46-3',
'05/09/7446',
'3319-31-1',
'83834-59-7',
'38083-17-9',
'71617-10-2',
'61788-44-1',
'115-86-6',
'10222-01-2',
'130328-19-7',
'75-21-8',
'1222-05-5',
'128-37-0',
'80-54-6',
'1453-58-3',
'77-40-7',
'84-61-7',
'265647-11-8',
'130328-20-0',
'137-26-8',
'35554-44-0',
'53988-10-6',
'61617-00-3',
'121158-58-5',
'80-05-7',
'21850-44-2',
'97416-84-7',
'96-76-4',
'118-56-9',
'13595-25-0',
'36861-47-9',
'78-59-1',
'80-05-7',
'7790-98-9',
'75-09-2',
'68479-98-1',
'7601-89-0',
'106-44-5',
'77-90-7',
'5384-21-4',
'95-14-7',
'80-08-0',
'628-96-6',
'693-21-0',
'108-46-3',
'57583-54-7',
'15087-24-8',
'98-54-4',
'80-46-6',
'17527-29-6',
'2144-53-8',
'6864-37-5',
'98-95-3',
'100-21-0',
'77-94-1',
'79-94-7',
'25068-38-6',
'119-36-8',
'131-57-7',
'288-88-0',
'118-82-1',
'96-69-5',
'80-09-1',
'330-54-1',
'99-76-3',
'94-13-3',
'1634-04-4',
'25013-16-5',
'137-30-4'
)
AND sub.[CAS nr] NOT IN ('64-17-5')
)
OR sub.[DK Oploesningsmiddel] = 1
)
)
THEN 1
ELSE 0
END,
SupplierName = sup.SupplierName,
Usage = a.Anvendelsesområde
,p.[Dessin nr] as ProductNumber
FROM APB_Brugssted_RM_TBL apb
INNER JOIN ProduktTabel p ON apb.ProduktID = p.ProduktID
LEFT JOIN RM_supplier rms ON p.ProduktID = rms.ProduktID
LEFT JOIN SupplierTBL sup ON rms.Supplier = sup.supplierID
INNER JOIN T_Categories c ON apb.category_amount_year_unit_id = c.id
INNER JOIN ProduktSubstansTabel ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel sub ON ps.SubstansID = sub.SubstansID
INNER JOIN KlassifikatiONsTabel_GHS sc ON sub.SubstansID = sc.SubstansID
left outer join KlassifikationsTabel_RM_CLP kc on kc.ProduktID = p.ProduktID
INNER JOIN T_Hierarchys h ON apb.t_hierarchy_id = h.id
INNER JOIN dbo.HierarchyPaths() ppp ON ppp.Id = h.id
AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0, 0) > 0
THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
LEFT JOIN Anvendelsesområde_RM a ON a.AnvendelsesområdeID = p.AnvendelsesområdeID
WHERE p.Aktiv = 1 and apb.APB_RM_TBL IS NULL
AND (
ISNULL(rms.Supplier, 0) = CASE
WHEN ISNULL(@p1, 0) > 0
THEN @p1
ELSE ISNULL(rms.Supplier, 0)
END
)
-- Allergy
AND (
(
(
(ISNULL(ps.Amount, 0) >= ISNULL(sc.H334, 200))
OR (ISNULL(ps.Amount, 0) >= ISNULL(sc.H317, 200))
)
OR (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H334, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H334, 0))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H317, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H317, 0))
)
)
)
-- Toxic
OR (
(
p.klassificering LIKE '%H300%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H310%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H330%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H301%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H301%' or sub.Substansklass_GHS LIKE '%H300%')
)
OR (
p.klassificering LIKE '%H311%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H311%' or sub.Substansklass_GHS LIKE '%H310%')
)
OR (
p.klassificering LIKE '%H331%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H331%' or sub.Substansklass_GHS LIKE '%H330%')
)
OR (
p.klassificering LIKE '%H370%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H370%')
)
OR (
p.klassificering LIKE '%H350%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H350%')
)
OR (
p.klassificering LIKE '%H360%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H360%')
)
OR (
p.klassificering LIKE '%H340%'
AND (isnull(kc.Ingen_beregning,0) = 1 or sub.Substansklass_GHS LIKE '%H340%')
)
)
-- Pregnant
OR (
(
p.klassificering LIKE '%H341%'
OR p.klassificering LIKE '%H350%'
OR p.klassificering LIKE '%H351%'
OR p.klassificering LIKE '%H360%'
OR p.klassificering LIKE '%H361%'
OR p.klassificering LIKE '%H350i%'
OR p.klassificering LIKE '%H370%'
OR p.klassificering LIKE '%H371%'
OR p.klassificering LIKE '%H372%'
OR p.klassificering LIKE '%H373%'
OR p.klassificering LIKE '%H340%'
OR (
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR sub.[substansklass_GHS] LIKE '%H341%'
OR sub.[substansklass_GHS] LIKE '%H350%'
OR sub.[substansklass_GHS] LIKE '%H351%'
OR sub.[substansklass_GHS] LIKE '%H360%'
OR sub.[substansklass_GHS] LIKE '%H361%'
OR sub.[substansklass_GHS] LIKE '%H370%'
OR sub.[substansklass_GHS] LIKE '%H371%'
OR sub.[substansklass_GHS] LIKE '%H372%'
OR sub.[substansklass_GHS] LIKE '%H373%'
)
OR (
p.klassificering LIKE '%H310%'
OR p.klassificering LIKE '%H311%'
OR p.klassificering LIKE '%H312%'
OR p.klassificering LIKE '%H362%'
OR (
sub.[DK Oploesningsmiddel] = 1
AND ps.Amount >= 1
OR sub.DKGraensevaerdiAnm LIKE '%H%'
)
OR sub.[substansklass_GHS] LIKE '%H310%'
OR sub.[substansklass_GHS] LIKE '%H311%'
OR sub.[substansklass_GHS] LIKE '%H312%'
OR sub.[substansklass_GHS] LIKE '%H362%'
)
OR (
sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'
)
OR (
sub.[CAS nr] IN (
'89997-63-7',
'66603-10-9',
'37353-75-6',
'94-26-8',
'2312-35-8',
'71868-10-5',
'80844-07-1',
'89997-63-7',
'420-04-2',
'64-18-6',
'10028-15-6',
'108-46-3',
'05/09/7446',
'3319-31-1',
'83834-59-7',
'38083-17-9',
'71617-10-2',
'61788-44-1',
'115-86-6',
'10222-01-2',
'130328-19-7',
'75-21-8',
'1222-05-5',
'128-37-0',
'80-54-6',
'1453-58-3',
'77-40-7',
'84-61-7',
'265647-11-8',
'130328-20-0',
'137-26-8',
'35554-44-0',
'53988-10-6',
'61617-00-3',
'121158-58-5',
'80-05-7',
'21850-44-2',
'97416-84-7',
'96-76-4',
'118-56-9',
'13595-25-0',
'36861-47-9',
'78-59-1',
'80-05-7',
'7790-98-9',
'75-09-2',
'68479-98-1',
'7601-89-0',
'106-44-5',
'77-90-7',
'5384-21-4',
'95-14-7',
'80-08-0',
'628-96-6',
'693-21-0',
'108-46-3',
'57583-54-7',
'15087-24-8',
'98-54-4',
'80-46-6',
'17527-29-6',
'2144-53-8',
'6864-37-5',
'98-95-3',
'100-21-0',
'77-94-1',
'79-94-7',
'25068-38-6',
'119-36-8',
'131-57-7',
'288-88-0',
'118-82-1',
'96-69-5',
'80-09-1',
'330-54-1',
'99-76-3',
'94-13-3',
'1634-04-4',
'25013-16-5',
'137-30-4'
)
AND sub.[CAS nr] NOT IN ('64-17-5')
)
OR sub.[DK Oploesningsmiddel] = 1
)
-- Cancer
OR (
(
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%'
)
AND ps.Amount >= 0.1
)
)
) a
GROUP BY a.[Path],
a.Product,
a.SupplierName,
a.Usage,
a.ProductNumber | Focus report for all non allocated chemicals | Delete | Edit |
| 127 | Recipient report | ReportsPage | 280 | | using System; public class $$ContainerClassName$$ { $$FieldDef$$ } | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | SELECT DISTINCT
Hierarchy = ppp.[Path],
ProductId = P.ProduktID,
Product = p.Produktnavn,
SupplierName = ISNULL(s.SupplierName,''),
Usage = a.Anvendelsesområde,
RecipientAdmin = p.Recipient,
RecipientWeb = c.category_value
,p.[Dessin nr] as ProductNumber
FROM ProduktTabel p
JOIN Anvendelsesområde_RM a
ON a.AnvendelsesområdeID = p.AnvendelsesområdeID
LEFT JOIN RM_supplier rms
ON p.produktid = rms.produktid
LEFT JOIN suppliertbl s
ON rms.supplier = s.supplierid
JOIN APB_Brugssted_RM_TBL apbrm
ON apbrm.ProduktID = p.ProduktID
JOIN T_Hierarchys h
ON h.id = apbrm.t_hierarchy_id
INNER JOIN T_Categories c
ON apbrm.category_recipient_id = C.Id
JOIN dbo.HierarchyPaths() ppp
ON ppp.Id = apbrm.t_hierarchy_id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
WHERE ISNULL(rms.Supplier,0) = CASE
WHEN ISNULL(@p1,0) >0 THEN @p1
ELSE ISNULL(rms.Supplier,0)
END | Recipient report | Delete | Edit |
| 110 | Terror list report | ReportsPage | 285 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT
Hierarchy = ppp.[Path],
Product = p.Produktnavn,
ProductClassification = p.klassificering
FROM ProduktTabel p
JOIN APB_Brugssted_RM_TBL apb
ON p.ProduktID = apb.ProduktID
JOIN T_Hierarchys h
ON apb.t_hierarchy_id = h.id
JOIN dbo.HierarchyPaths() ppp
ON ppp.Id = h.id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
WHERE p.klassificering LIKE '%H300%'
OR p.klassificering LIKE '%H301%'
OR p.klassificering LIKE '%H310%'
OR p.klassificering LIKE '%H311%'
OR p.klassificering LIKE '%H330%'
OR p.klassificering LIKE '%H331%'
| Lists all chemical named on the Terror list for a given location and its sub locations. | Delete | Edit |
| 166 | ABC Råvarer-Substanser-Ikke Tilknyttede | ReportsPage | 290 | | using System; public class $$ContainerClassName$$ { $$FieldDef$$ } | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | /*Ikke tilknyttede*/
select
pt.ProduktID as RåvareID,
pt.ProduktNavn as Råvarenavn,
am.Anvendelsesområde,
st.[navn systematisk] as Substans,
isnull(pst.amount,0) as Koncentration_af_substans_i_råvare,
st.SubstansID as SubstansId,
ISNULL(st.[CAS nr],'') as CAS_nr,
case isnull(st.InternalCode,'')
when 'A' then 'A'
when 'B' then 'B'
when 'C' then 'C'
when 'D' then 'D'
else 'Ikke Vurderet' end as A_B_eller_C_stof,
isnull(pt.recipient,'') as Recipient_AO_ADM,
ISNULL(wct.AffaldDK,'') as Affaldsgruppe
,SUP.SupplierName as [Leverandør]
,pt.[Dessin nr] as ProductNumber
from
Substanstabel st
left outer join produktsubstanstabel pst on pst.substansid = st.substansid
inner join ProduktTabel pt on pt.produktid = pst.ProduktID
inner join Anvendelsesområde_RM am on am.AnvendelsesområdeID = pt.AnvendelsesområdeID
left outer join ABC_TBL abc on abc.cas = st.[CAS nr]
left outer join WasteCatTBL wct on wct.ID = pt.Affaldsgruppe
left outer join RM_supplier RM_SUP on RM_SUP.ProduktID = pt.ProduktID
left outer join SupplierTBL SUP on SUP.supplierID = RM_SUP.Supplier
where not pt.ProduktID in (select produktid from APB_Brugssted_RM_TBL) and pt.Aktiv = 1
order by pt.ProduktNavn
| ABC: Råvarer-Substanser-Ikke Tilknyttede | Delete | Edit |
| 164 | ABC Råvarer-Substanser-Tilknyttede | ReportsPage | 295 | | using System; public class $$ContainerClassName$$ { $$FieldDef$$ } | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | /*
Changed 11-07-2018
- Added Supplier
- Filtered out products that are not Active
*/
DECLARE @ExcludeHirList TABLE (id INT)
insert into @ExcludeHirList select Id from HierarchyPaths() where Path like '%0 Undervisning%' or Path like '%Chymeia Hovedkontor%'
select
pt.ProduktID as RåvareID,
pt.ProduktNavn as Råvarenavn,
am.Anvendelsesområde,
st.[navn systematisk] as Substans,
isnull(pst.amount,0) as Koncentration_af_substans_i_råvare,
st.SubstansID as SubstansId,
ISNULL(st.[CAS nr],'') as CAS_nr,
case isnull(st.InternalCode,'')
when 'A' then 'A'
when 'B' then 'B'
when 'C' then 'C'
when 'D' then 'D'
else 'Ikke Vurderet' end as A_B_eller_C_stof,
(select Path from HierarchyPaths() where Id = hir.id) as Brugssted,
addr.[address] as Adresse,
isnull(pt.recipient,'') as Recipient_AO_ADM,
cat.category_value as Udledningsvej,
ISNULL(wct.AffaldDK,'') as Affaldsgruppe
,SUP.SupplierName as [Leverandør]
,pt.[Dessin nr] as ProductNumber
from
Substanstabel st
left outer join produktsubstanstabel pst on pst.substansid = st.substansid
inner join ProduktTabel pt on pt.produktid = pst.ProduktID
inner join Anvendelsesområde_RM am on am.AnvendelsesområdeID = pt.AnvendelsesområdeID
inner join APB_Brugssted_RM_TBL abr on abr.ProduktID = pt.produktid
inner join T_Hierarchys hir on hir.id = abr.t_hierarchy_id
inner join T_Hierarchy_Informations hirInfo on hirInfo.t_hierarchy_id = hir.id
inner join T_Addresses addr on addr.id = hirInfo.t_address_id
inner join T_Categories cat on cat.id = abr.category_recipient_id
left outer join WasteCatTBL wct on wct.ID = pt.Affaldsgruppe
left outer join RM_supplier RM_SUP on RM_SUP.ProduktID = pt.ProduktID
left outer join SupplierTBL SUP on SUP.supplierID = RM_SUP.Supplier
where
not hir.id in (select id from @ExcludeHirList) and pt.Aktiv = 1
order by hir.hierachy_name, pt.ProduktNavn | ABC: Råvarer-Substanser-Tilknyttede | Delete | Edit |
| 197 | ABC Råvarer-Substanser-Tilknyttede (NZ) | ReportsPage | 295 | | using System; public class $$ContainerClassName$$ { $$FieldDef$$ } | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | /*
Changed 16-03-2022
- Added IsDangerousWaste
- Removed some columns from the original ABC report
*/
DECLARE @ExcludeHirList TABLE (id INT)
insert into @ExcludeHirList select Id from HierarchyPaths() where Path like '%0 Undervisning%' or Path like '%Chymeia Hovedkontor%'
select
pt.ProduktID as RåvareID,
pt.ProduktNavn as Råvarenavn,
am.Anvendelsesområde,
st.[navn systematisk] as Substans,
isnull(pst.amount,0) as Koncentration_af_substans_i_råvare,
st.SubstansID as SubstansId,
ISNULL(st.[CAS nr],'') as CAS_nr,
case isnull(st.InternalCode,'')
when 'A' then 'A'
when 'B' then 'B'
when 'C' then 'C'
when 'D' then 'D'
else 'Ikke Vurderet' end as A_B_eller_C_stof,
(select Path from HierarchyPaths() where Id = hir.id) as Brugssted,
addr.[address] as Adresse,
st.Substansklass_GHS as SubstanceClassification,
cast(case
when lower(isnull(sds.v94,'')) like '%Kemikaliet er farligt affald%' then 1
when lower(isnull(sds.v94,'')) like '%Produktet er omfattet af reglerne om farligt affald%' then 1
else 0
end as bit) as IsDangerousWaste
from
Substanstabel st
left outer join produktsubstanstabel pst on pst.substansid = st.substansid
inner join ProduktTabel pt on pt.produktid = pst.ProduktID
inner join Anvendelsesområde_RM am on am.AnvendelsesområdeID = pt.AnvendelsesområdeID
inner join APB_Brugssted_RM_TBL abr on abr.ProduktID = pt.produktid
inner join T_Hierarchys hir on hir.id = abr.t_hierarchy_id
inner join T_Hierarchy_Informations hirInfo on hirInfo.t_hierarchy_id = hir.id
inner join T_Addresses addr on addr.id = hirInfo.t_address_id
inner join SDSVersionTbl sds on sds.V98 = pt.ProduktID and sds.Released = 1 and sds.Type = '3_CLP' and sds.v99 = 'DK'
where
not hir.id in (select id from @ExcludeHirList) and pt.Aktiv = 1 | ABC: Råvarer-Substanser-Tilknyttede | Delete | Edit |
| 163 | ABC Substansdata | ReportsPage | 300 | | using System; public class $$ContainerClassName$$ { $$FieldDef$$ } | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | select distinct
st.SubstansId,
ISNULL(st.[CAS nr],'') as CAS_nr,
st.[navn systematisk] as Substans,
case isnull(st.InternalCode,'')
when 'A' then 'A'
when 'B' then 'B'
when 'C' then 'C'
when 'D' then 'D'
else 'Ikke Vurderet' end as A_B_eller_C_stof,
case when
st.SMILES like'%Hg%'
or st.SMILES like '%Pb%'
or st.SMILES like '%Cd%'
or st.SMILES like '%Cr%'
or st.SMILES like '%Ni%'
or st.SMILES like '%Cu%'
or st.SMILES like '%Zn%'
then st.SMILES else '' end as SMILES,
case when
st.STRUKTURFORMEL like'%Hg%'
or st.STRUKTURFORMEL like '%Pb%'
or st.STRUKTURFORMEL like '%Cd%'
or st.STRUKTURFORMEL like '%Cr%'
or st.STRUKTURFORMEL like '%Ni%'
or st.STRUKTURFORMEL like '%Cu%'
or st.STRUKTURFORMEL like '%Zn%'
then st.STRUKTURFORMEL else '' end as STRUKTURFORMEL,
case when
st.substansklass_ghs like 'h35%'
or st.substansklass_ghs like '%H35%'
or st.substansklass_ghs like '%H36%'
or st.substansklass_ghs like '%H340%'
or st.substansklass_ghs like '%H370%'
or st.substansklass_ghs like '%H372%'
or st.substansklass_ghs like '%H373%'
then 'Ja' else 'Nej' end as A_CMR_STOT,
case when
st.substansklass_ghs like '%H412%'
then 'Ja' else 'Nej' end as B_H412,
case when
st.substansklass_ghs like '%H400, H410%'
then 'Ja' else 'Nej' end as H400_H410,
case when
st.substansklass_ghs like '%H411%'
then 'Ja' else 'Nej' end as H411,
/*TODO:*/
-- EC-Værdi
isnull(ett.result,'') as EC_Værdi,
-- M-Faktor
isnull(ktg.M_faktor,1) as M_Faktor_Akut,
isnull(ktg.M_chronic,1) as M_Faktor_Kronisk,
-- Nedbrydelig (Ja/Nej)
case when st.Biodegradable = 1 then 'Ja'
else 'Nej' end as Nedbrydelig,
-- Basseret på
/*st.biodegra*/
isnull(ebt.Method,'') as Baseret_På, /*ID?*/
isnull(st.biodegradableResult,'') as Resultat,
-- resultat!
-- Flygtig (Ja/Nej)
case when st.FlashCalc = 1 then 'Ja'
else 'Nej' end as [Flygtig],
-- BCF-Værdi
isnull(st.bioaccuBCF,0) as BCF_Værdi,
-- PNEC
--isnull(ett.PNEC,'') as PNEC,
(select isnull(PNEC,'') from EcotoxTbl where SubstansID = st.SubstansID and (PNEC_exposure = 'Marine water' or PNEC_exposure = '6')) as [PNEC],
st.substansklass_ghs as Klassificering
from
Substanstabel st
left outer join KlassifikationsTabel_GHS ktg on ktg.SubstansID = st.SubstansID
left outer join Eco_Biodegra_test ebt on ebt.bionedbrydeligID = st.biodegra
left outer join EcotoxTbl ett on ett.SubstansID = st.SubstansID and (ett.Include = 1 or not ett.PNEC is null)
order by st.[navn systematisk] | | Delete | Edit |
| 126 | SDS Attachment Overview | ReportsPage | 310 | | using System; public class $$ContainerClassName$$ { $$FieldDef$$ } | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | SELECT distinct
ppp.[Path] as Hierarchy,
pt.ProduktID as ProductID,
ltrim(pt.produktnavn) as productname,
cast(case len(isnull(f.file_name,''))
when 0 then
0
else
1
end as bit) as HasSDSFile,
cast(case (select count(VersionID) from sdsversiontbl where v98 = pt.ProduktID)
when 0 then 0
else 1
end as bit) as APBData,
cast(case (select count(VersionID) from sdsversiontbl where v98 = pt.ProduktID and released = 1)
when 0 then 0
else 1
end as bit) as APBDataReleased,
(SELECT (STUFF((SELECT (', ' + s.SupplierName)
FROM RM_supplier AS rms
INNER JOIN SupplierTBL AS s ON s.supplierID = rms.Supplier
WHERE rms.ProduktID = pt.ProduktID
FOR XML PATH ('')), 1, 2, ''))) AS Suppliers
,pt.[Dessin nr] as ProductNumber
from ProduktTabel pt
left outer join T_ProductFiles pf on pf.ProductId = pt.ProduktID
left outer join T_Files f on f.id = pf.FileId
JOIN APB_Brugssted_RM_TBL apbrm ON apbrm.ProduktID = pt.ProduktID
JOIN dbo.HierarchyPaths() ppp ON ppp.Id = apbrm.t_hierarchy_id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
order by Hierarchy,productname | Shows a list of Products and their SDS Status (SDS Attached, Created Date and Modified Date) | Delete | Edit |
| 185 | SDS List | ReportsPage | 310 | | using System; public class $$ContainerClassName$$ { $$FieldDef$$ } | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | SELECT
pt.ProduktID,
ltrim(pt.produktnavn) as Produktnavn,
isnull(f.file_name,'') as SDSFile,
pf.CreatedDate as SDSCreatedDate,
pf.SavedDate as SDSSavedDate,
isnull(f.file_guid,'') as SDSFileGuid,
isnull(f.file_extension,'') as SDSFileExt,
cast(case (select count(VersionID) from sdsversiontbl where v98 = pt.ProduktID)
when 0 then 0
else 1
end as bit) as APBData,
cast(case (select count(VersionID) from sdsversiontbl where v98 = pt.ProduktID and released = 1)
when 0 then 0
else 1
end as bit) as APBDataReleased,
isnull(supplier.SupplierName,'') as Supplier
from ProduktTabel pt
left outer join T_ProductFiles pf on pf.ProductId = pt.ProduktID
left outer join T_Files f on f.id = pf.FileId
left outer join RM_supplier rm_s on rm_s.ProduktID = pt.ProduktID
left outer join SupplierTBL supplier on supplier.supplierID = rm_s.Supplier
where pt.Aktiv = 1 and pf.Deprecated = 0
order by pf.CreatedDate, pf.SavedDate | Viser liste over alle produkter , og om de har tilknyttede APB Data (SDSVersionTbl data), samt datoer mv. | Delete | Edit |
| 123 | Total Report | ReportsPage | 500 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | DECLARE @toxicProducts TABLE (id INT);
DECLARE @cancerProducts TABLE (id INT);
DECLARE @allergyProducts TABLE (id INT);
DECLARE @pregnantProducts TABLE (id INT);
/* TOXIC PREP */
INSERT INTO @toxicProducts
SELECT DISTINCT p.ProduktId
FROM ProduktTabel p
INNER JOIN ProduktSubstansTabel AS ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel AS sub ON ps.SubstansID = sub.SubstansID
WHERE (
p.klassificering LIKE '%H300%'
AND sub.Substansklass_GHS LIKE '%H300%'
)
OR (
p.klassificering LIKE '%H310%'
AND sub.Substansklass_GHS LIKE '%H310%'
)
OR (
p.klassificering LIKE '%H330%'
AND sub.Substansklass_GHS LIKE '%H330%'
)
OR (
p.klassificering LIKE '%H301%'
AND sub.Substansklass_GHS LIKE '%H301%'
)
OR (
p.klassificering LIKE '%H311%'
AND sub.Substansklass_GHS LIKE '%H311%'
)
OR (
p.klassificering LIKE '%H331%'
AND sub.Substansklass_GHS LIKE '%H331%'
)
OR (
p.klassificering LIKE '%H370%'
AND sub.Substansklass_GHS LIKE '%H370%'
)
OR (
p.klassificering LIKE '%H350%'
AND sub.Substansklass_GHS LIKE '%H350%'
)
OR (
p.klassificering LIKE '%H360%'
AND sub.Substansklass_GHS LIKE '%H360%'
)
OR (
p.klassificering LIKE '%H340%'
AND sub.Substansklass_GHS LIKE '%H340%'
)
OR (
p.klassificering LIKE '%R26%'
AND sub.Substansklass LIKE '%R26%'
)
OR (
p.klassificering LIKE '%R27%'
AND sub.Substansklass LIKE '%R27%'
)
OR (
p.klassificering LIKE '%R28%'
AND sub.Substansklass LIKE '%R28%'
)
OR (
p.klassificering LIKE '%R39%'
AND sub.Substansklass LIKE '%R39%'
)
OR (
p.klassificering LIKE '%R23%'
AND sub.Substansklass LIKE '%R23%'
)
OR (
p.klassificering LIKE '%R24%'
AND sub.Substansklass LIKE '%R24%'
)
OR (
p.klassificering LIKE '%R25%'
AND sub.Substansklass LIKE '%R25%'
)
OR (
p.klassificering LIKE '%R45%'
AND sub.Substansklass LIKE '%R45%'
)
OR (
p.klassificering LIKE '%R49%'
AND sub.Substansklass LIKE '%R49%'
)
OR (
p.klassificering LIKE '%R46%'
AND sub.Substansklass LIKE '%R46%'
)
OR (
p.klassificering LIKE '%R60%'
AND sub.Substansklass LIKE '%R60%'
)
OR (
p.klassificering LIKE '%R61%'
AND sub.Substansklass LIKE '%R61%'
)
/* CANCER PREP */
INSERT INTO @cancerProducts
SELECT DISTINCT p.ProduktId
FROM ProduktTabel p
INNER JOIN ProduktSubstansTabel AS ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel AS sub ON ps.SubstansID = sub.SubstansID
WHERE (
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
sub.[DK Kraeftlisten] = 1
OR sub.[CancerDK_17] = 1
OR sub.[CancerDK_27] = 1
OR sub.[CancerDK_29_32] = 1
OR sub.[CancerDK_33] = 1
OR sub.Substansklass_GHS LIKE '%CARC%'
OR sub.Substansklass_GHS LIKE '%H340%'
)
AND (
(
sub.[DK Kraeftlisten] = 1
AND ps.Amount >= 0.1
)
OR p.klassificering LIKE '%CARC%'
OR p.klassificering LIKE '%muta%'
)
/* ALLERGY PREP */
INSERT INTO @allergyProducts
SELECT DISTINCT p.ProduktId
FROM ProduktTabel p
INNER JOIN ProduktSubstansTabel AS ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel AS sub ON ps.SubstansID = sub.SubstansID
INNER JOIN KlassifikatiONsTabel_GHS sc ON sub.SubstansID = sc.SubstansID
WHERE (
(ISNULL(ps.Amount, 0) >= ISNULL(sc.H334, 200))
OR (ISNULL(ps.Amount, 0) >= ISNULL(sc.H317, 200))
)
OR (
(
(ISNULL(sc.H334, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H334, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H334, 0))
)
OR (
(ISNULL(sc.H317, 0) > 0)
AND (ISNULL(ps.Amount, 0) >= (ISNULL(sc.H317, 1100) / 10))
AND (ISNULL(ps.Amount, 0) < ISNULL(sc.H317, 0))
)
)
/* PREGNANCY PREP */
INSERT INTO @pregnantProducts
SELECT DISTINCT p.ProduktId
FROM ProduktTabel p
INNER JOIN ProduktSubstansTabel AS ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel AS sub ON ps.SubstansID = sub.SubstansID
WHERE (
sub.SecSub <> 'AT-GV'
OR ISNULL(sub.SecSub, '') = ''
)
AND (
sub.Substansklass_GHS LIKE '%H340%'
OR sub.Substansklass_GHS LIKE '%H341%'
OR sub.Substansklass_GHS LIKE '%H350%'
OR sub.Substansklass_GHS LIKE '%H351%'
OR sub.Substansklass_GHS LIKE '%H360%'
OR sub.Substansklass_GHS LIKE '%H361%'
OR sub.Substansklass_GHS LIKE '%H370%'
OR sub.Substansklass_GHS LIKE '%H371%'
OR sub.Substansklass_GHS LIKE '%H372%'
OR sub.Substansklass_GHS LIKE '%H373%'
OR sub.Substansklass_GHS LIKE '%H312%'
OR sub.Substansklass LIKE '%R39%'
OR sub.Substansklass LIKE '%R40%'
OR sub.Substansklass LIKE '%R45%'
OR sub.Substansklass LIKE '%R46%'
OR sub.Substansklass LIKE '%R48%'
OR sub.Substansklass LIKE '%R49%'
OR sub.Substansklass LIKE '%R60%'
OR sub.Substansklass LIKE '%R61%'
OR sub.Substansklass LIKE '%R62%'
OR sub.Substansklass LIKE '%R63%'
OR sub.Substansklass LIKE '%R64%'
OR sub.Substansklass LIKE '%R68%'
OR sub.[DK Kraeftlisten] = 1
OR (
sub.[DK Oploesningsmiddel] = 1
AND sub.DKGraensevaerdiAnm LIKE '%H%'
AND ps.Amount >= 1
)
OR sub.strukturformel LIKE '%HG%'
OR sub.strukturformel LIKE '%PB%'
OR sub.strukturformel LIKE '%CD%'
)
SELECT DISTINCT ppp.[Path] AS Hierarchy,
p.Produktnavn AS Product,
ISNULL(p.klassificering, '-') AS ProductClassification,
ISNULL(s.SupplierName, '') AS SupplierName,
a.Anvendelsesområde AS Usage,
(
STUFF((
SELECT (', ' + sub.[CAS nr])
FROM ProduktSubstansTabel AS prs
INNER JOIN SubstansTabel AS sub ON sub.SubstansID = prs.SubstansID
WHERE prs.ProduktID = p.ProduktID
FOR XML PATH('')
), 1, 2, '')
) AS CAS_nr,
cast(CASE
WHEN (
EXISTS (
SELECT id
FROM @toxicProducts
WHERE id = p.ProduktID
)
)
THEN 1
ELSE 0
END AS BIT) AS ToxicProduct,
cast(CASE
WHEN (
EXISTS (
SELECT id
FROM @cancerProducts
WHERE id = p.ProduktID
)
)
THEN 1
ELSE 0
END AS BIT) AS CancerProduct,
cast(CASE
WHEN (
EXISTS (
SELECT id
FROM @allergyProducts
WHERE id = p.ProduktID
)
)
THEN 1
ELSE 0
END AS BIT) AS AllergyProduct,
cast(CASE
WHEN (
EXISTS (
SELECT id
FROM @pregnantProducts
WHERE id = p.ProduktID
)
)
THEN 1
ELSE 0
END AS BIT) AS PregnantProduct,
FireMixedWithWater = CAST(ISNULL(p.BlandbaH2O, 0) AS BIT),
FlashPoint = CAST(p.Flammepunkt AS INT),
FlameClass = CASE
WHEN p.[Fysisk tilstand] <> 'Flydende'
THEN '-'
WHEN p.Flammepunkt < 21
THEN 'I - '
WHEN p.Flammepunkt >= 21
AND p.Flammepunkt <= 55
THEN 'II -'
WHEN p.Flammepunkt > 55
AND p.Flammepunkt < 99
THEN 'III -'
ELSE '-'
END + CASE
WHEN p.[Fysisk tilstand] <> 'Flydende'
THEN ''
WHEN ISNULL(p.BlandbaH2O, 0) = 1
THEN '2'
ELSE '1'
END,
ProductPhysicalForm = p.[Fysisk tilstand],
ProductStock = sto.LagertempDK,
WasteGroup = w.AffaldDK
,p.[Dessin nr] as ProductNumber
FROM ProduktTabel AS p
INNER JOIN Anvendelsesområde_RM AS a ON a.AnvendelsesområdeID = p.AnvendelsesområdeID
LEFT JOIN RM_supplier AS rms ON p.produktid = rms.produktid
INNER JOIN suppliertbl AS s ON rms.supplier = s.supplierid
INNER JOIN APB_Brugssted_RM_TBL AS apbrm ON apbrm.ProduktID = p.ProduktID
INNER JOIN T_Hierarchys AS h ON h.id = apbrm.t_hierarchy_id
INNER JOIN dbo.HierarchyPaths() AS ppp ON ppp.Id = apbrm.t_hierarchy_id
AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0, 0) > 0
THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
INNER JOIN ProduktSubstansTabel AS ps ON p.ProduktID = ps.ProduktID
INNER JOIN SubstansTabel AS sub ON ps.SubstansID = sub.SubstansID
INNER JOIN KlassifikatiONsTabel_GHS AS sc ON sub.SubstansID = sc.SubstansID
LEFT JOIN WasteCatTBL w ON p.Affaldsgruppe = w.ID
LEFT JOIN Oti_lagertempTBL sto ON p.lagertemp = sto.LagerTempID
WHERE rms.Supplier = CASE
WHEN ISNULL(@p1, 0) > 0
THEN @p1
ELSE rms.Supplier
END;
| Lists all chemicals on a location and all its sub locations. | Delete | Edit |
| 155 | Chemical list with transport informations | ReportsPage | 550 | | using System;public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | SELECT p.produktnavn,
isnull(p.etiketlink, '') AS synonyme,
isnull(t.UN, '') AS UN,
isnull(t.Pck_grp, '') AS PG,
isnull(t.FareKlass, '') AS CLASS,
isnull(t.ProperShipppingADR, '') AS ProperShippingName
,p.[Dessin nr] as ProductNumber
FROM produkttabel AS p
LEFT OUTER JOIN
Transport_ADR_RID_RM AS t
ON t.ProduktID = p.ProduktID | Total list of all Chemicals with transport informations. | Delete | Edit |
| 149 | Distinct Chemical Report | ReportsPage | 600 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | SELECT DISTINCT
Product = p.Produktnavn,
ProductClassification = ISNULL(p.klassificering,'-'),
SupplierName = ISNULL(s.SupplierName,''),
Usage = a.Anvendelsesområde,
(STUFF((SELECT (', ' + sub.[CAS nr])
FROM ProduktSubstansTabel prs
inner join SubstansTabel sub on sub.SubstansID = prs.SubstansID
WHERE
prs.ProduktID = p.ProduktID
FOR XML PATH('')), 1, 2, '')) AS CAS_nr,
' ' + (select top 1 Item from GHSStringToImageList(et.piktogram_CLP,'24','background-color:white; padding:4px;border:solid 1px #c0c0c0;margin:1px;')) + ' ' as picto_noexport_imgsrc,
p.[Fysisk tilstand] as Tilstandsform,
(STUFF((SELECT ('' + 'SDS')
from T_Files where id in (select top 1 fileid from T_ProductFiles where ProductId = p.produktid and not Deprecated = 1 and US = 0 order by CreatedDate desc)
FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'),1, 0, N'')) as SDSLinks_noexport
,p.[Dessin nr] as ProductNumber
FROM ProduktTabel p
JOIN Anvendelsesområde_RM a ON a.AnvendelsesområdeID = p.AnvendelsesområdeID
LEFT JOIN RM_supplier rms ON p.produktid = rms.produktid
JOIN suppliertbl s ON rms.supplier = s.supplierid
JOIN APB_Brugssted_RM_TBL apbrm ON apbrm.ProduktID = p.ProduktID
LEFT JOIN EtiketTBL et on et.ProduktID = p.ProduktID and sprog = 'DK'
WHERE rms.Supplier = CASE
WHEN ISNULL(@p0,0) >0 THEN @p0
ELSE rms.Supplier
END | Lists all chemicals distinct for all suppliers. | Delete | Edit |
| 186 | Own Labels | ReportsPage | 650 | | using System;public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | select distinct
ppp.[Path] as Hierarchy,
p.produktnavn,
su.SupplierName,
p.LeverandørEtiket as overskriv,
olw.CanonicalName as Why,
p.[Dessin nr] as ProductNumber
from
ProduktTabel p
left outer join RM_supplier rm on rm.ProduktID = p.ProduktID
left outer join SupplierTBL su on su.supplierID = rm.supplier
left outer join OwnLabelWhy_TBL olw on olw.ID = rm.OwnLabelWhy
JOIN APB_Brugssted_RM_TBL apbrm ON apbrm.ProduktID = p.ProduktID
JOIN dbo.HierarchyPaths() ppp ON ppp.Id = apbrm.t_hierarchy_id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
where p.LeverandørEtiket = 1 | Displays a list of own labeled products with explanation why the supplier label is overwritten. | Delete | Edit |
| 187 | Own Labels one Hierarchy | ReportsPage | 650 | | using System;public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | select distinct
p.produktnavn,
su.SupplierName,
p.LeverandørEtiket as overskriv,
olw.CanonicalName as Why,
p.[Dessin nr] as ProductNumber
from
ProduktTabel p
left outer join RM_supplier rm on rm.ProduktID = p.ProduktID
left outer join SupplierTBL su on su.supplierID = rm.supplier
left outer join OwnLabelWhy_TBL olw on olw.ID = rm.OwnLabelWhy
INNER JOIN APB_Brugssted_RM_TBL AS apbrm ON apbrm.ProduktID = p.ProduktID
INNER JOIN T_Hierarchys AS h ON apbrm.t_hierarchy_id = @p0 AND h.id = apbrm.t_hierarchy_id
where p.LeverandørEtiket = 1 | Displays a list of own labeled products with explanation why the supplier label is overwritten. | Delete | Edit |
| 195 | Chemical Report wo suppliers | ReportsPage | 700 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | declare @aoLang as varchar(2) = case lower(@p1) when 'da-dk' then 'dk' else 'en' end;
SELECT DISTINCT
Hierarchy = ppp.[Path],
Product = p.Produktnavn,
ProductClassification = isnull(CONCAT(isnull(p.klassificering,''),' ', dbo.GetPatternMatches('%EUH[0-9][0-9][0-9]%',et.Andenmaerkning_CLP,6, ' ')),'-'),
Usage = a.Anvendelsesområde,
CASE
WHEN sds.v30 IS NULL
OR sds.v30 LIKE 'Ikke anvendelig%'
OR sds.v30 LIKE '-'
OR sds.v30 LIKE 'Not applicable%'
OR sds.v30 LIKE 'EUH210'
OR sds.v30 LIKE '-¤'
OR sds.v30 LIKE '¤'
OR sds.v30 LIKE 'Ingen¤%'
THEN ''
ELSE REPLACE(REPLACE(sds.v30, 'Sikkerhedsdatablad kan på anmodning rekvireres. (EUH210)', ''), '¤', '')
END AS OtherRemark,
(STUFF((SELECT (', ' + sub.[CAS nr])
FROM ProduktSubstansTabel prs
inner join SubstansTabel sub on sub.SubstansID = prs.SubstansID
WHERE
prs.ProduktID = p.ProduktID
FOR XML PATH('')), 1, 2, '')) AS CAS_nr,
' ' + (select top 1 Item from GHSStringToImageList(et.piktogram_CLP,'24','background-color:white; padding:4px;border:solid 1px #c0c0c0;margin:1px;')) + ' ' as picto_noexport_imgsrc,
p.[Fysisk tilstand] as Tilstandsform,
(STUFF((SELECT ('' + 'SDS')
from T_Files where id in (select top 1 fileid from T_ProductFiles where ProductId = p.produktid and not Deprecated = 1 and US = 0 order by CreatedDate desc)
FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'),1, 0, N'')) as SDSLinks_noexport,
apbrm.Departments
, p.[Dessin nr] as ProductNumber
FROM ProduktTabel p
JOIN Anvendelsesområde_RM a ON a.AnvendelsesområdeID = p.AnvendelsesområdeID
JOIN APB_Brugssted_RM_TBL apbrm ON apbrm.ProduktID = p.ProduktID
LEFT JOIN SDSVersionTbl AS sds ON p.ProduktID = sds.v98 and sds.Released = 1 and lower(sds.v99) = @aoLang and lower(sds.type) = '3_clp'
JOIN T_Hierarchys h ON h.id = apbrm.t_hierarchy_id
LEFT JOIN EtiketTBL et on et.ProduktID = p.ProduktID and lower(sprog) = @aoLang
JOIN dbo.HierarchyPaths() ppp ON ppp.Id = apbrm.t_hierarchy_id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END | Lists all chemicals on a location and all its sub locations (without suppliers) | Delete | Edit |
| 179 | REACH Report | ReportsPage | 730 | | using System; public class $$ContainerClassName$$ { $$FieldDef$$ } | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | select distinct
ppp.[Path] as Hierarchy,
p.Produktnavn as Product,
st.[Navn Systematisk] as Substance, st.[CAS nr] as CAS,
pst.Amount,
pst.Interval,
CAST(case when st.[CAS nr] like aut.Cas then 1 else 0 end as bit) as AUTH,
CAST(case when st.[CAS nr] like kd.Cas then 1 else 0 end as bit) as SVHC,
CAST(case when st.[CAS nr] like ax.Cas then 1 else 0 end as bit) as REST,
p.[Dessin nr] as ProductNumber
from
ProduktTabel p
inner join ProduktSubstansTabel pst on pst.ProduktID = p.ProduktID
inner join SubstansTabel st on st.SubstansID = pst.SubstansID
left outer join Aut_tbl_6_5 aut on aut.Cas like st.[CAS nr]
left outer join KandidatTBL kd on kd.cas like st.[CAS nr]
left outer join AnnexXVIIRestrictions ax on ax.cas like st.[CAS nr]
JOIN APB_Brugssted_RM_TBL apbrm ON apbrm.ProduktID = p.ProduktID
JOIN dbo.HierarchyPaths() ppp ON ppp.Id = apbrm.t_hierarchy_id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
where
st.[CAS nr] like aut.Cas or st.[CAS nr] like kd.Cas or st.[CAS nr] like ax.Cas | Shows a list of all products that contains substances that is represented in either Auth, SVHC or REST. | Delete | Edit |
| 180 | REACH Report for one hierarchy | ReportsPage | 730 | | using System; public class $$ContainerClassName$$ { $$FieldDef$$ } | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | select distinct
p.Produktnavn as Product,
st.[Navn Systematisk] as Substance, st.[CAS nr] as CAS,
pst.Amount,
pst.Interval,
CAST(case when st.[CAS nr] like aut.Cas then 1 else 0 end as bit) as AUTH,
CAST(case when st.[CAS nr] like kd.Cas then 1 else 0 end as bit) as SVHC,
CAST(case when st.[CAS nr] like ax.Cas then 1 else 0 end as bit) as REST,
p.[Dessin nr] as ProductNumber
from
ProduktTabel p
inner join ProduktSubstansTabel pst on pst.ProduktID = p.ProduktID
inner join SubstansTabel st on st.SubstansID = pst.SubstansID
left outer join Aut_tbl_6_5 aut on aut.Cas like st.[CAS nr]
left outer join KandidatTBL kd on kd.cas like st.[CAS nr]
left outer join AnnexXVIIRestrictions ax on ax.cas like st.[CAS nr]
INNER JOIN APB_Brugssted_RM_TBL AS apbrm ON apbrm.ProduktID = p.ProduktID
INNER JOIN T_Hierarchys AS h ON apbrm.t_hierarchy_id = @p0 AND h.id = apbrm.t_hierarchy_id
where
st.[CAS nr] like aut.Cas or st.[CAS nr] like kd.Cas or st.[CAS nr] like ax.Cas | Shows a list of all products that contains substances that is represented in either Auth, SVHC or REST for a single hierarchy. | Delete | Edit |
| 226 | Chemical Report for Dangerous Goods | ReportsPage | 1000 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | declare @aoLang as varchar(2) = case lower(@p2) when 'da-dk' then 'dk' else 'en' end;
SELECT DISTINCT
Hierarchy = ppp.[Path],
Product = p.Produktnavn,
ProductClassification = isnull(CONCAT(isnull(p.klassificering,''),' ', dbo.GetPatternMatches('%EUH[0-9][0-9][0-9]%',et.Andenmaerkning_CLP,6, ' ')),'-'),
SupplierName = ISNULL(s.SupplierName,''),
--Usage = a.Anvendelsesområde,
adr.UN,
(STUFF((SELECT (', ' + sub.[CAS nr])
FROM ProduktSubstansTabel prs
inner join SubstansTabel sub on sub.SubstansID = prs.SubstansID
WHERE
prs.ProduktID = p.ProduktID
FOR XML PATH('')), 1, 2, '')) AS CAS_nr,
' ' + (select top 1 Item from GHSStringToImageList(et.piktogram_CLP,'24','background-color:white; padding:4px;border:solid 1px #c0c0c0;margin:1px;')) + ' ' as picto_noexport_imgsrc,
p.[Fysisk tilstand] as Tilstandsform,
(STUFF((SELECT ('' + 'SDS')
from T_Files where id in (select top 1 fileid from T_ProductFiles where ProductId = p.produktid and not Deprecated = 1 and US = 0 order by CreatedDate desc)
FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'),1, 0, N'')) as SDSLinks_noexport,
apbrm.Departments
,p.[Dessin nr] as ProductNumber
FROM ProduktTabel p
--JOIN Anvendelsesområde_RM a ON a.AnvendelsesområdeID = p.AnvendelsesområdeID
LEFT JOIN RM_supplier rms ON p.produktid = rms.produktid
JOIN suppliertbl s ON rms.supplier = s.supplierid
JOIN APB_Brugssted_RM_TBL apbrm ON apbrm.ProduktID = p.ProduktID
JOIN T_Hierarchys h ON h.id = apbrm.t_hierarchy_id
inner join Transport_ADR_RID_RM adr on adr.ProduktID = p.ProduktID
LEFT JOIN EtiketTBL et on et.ProduktID = p.ProduktID and lower(sprog) = @aoLang
JOIN dbo.HierarchyPaths() ppp ON ppp.Id = apbrm.t_hierarchy_id AND ppp.PathIDs LIKE CASE
WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%'
ELSE '%'
END
WHERE ISNULL(rms.Supplier, 0) = CASE
WHEN ISNULL(@p1, 0) > 0
THEN @p1
ELSE ISNULL(rms.Supplier, 0)
END
and not ISNULL(adr.UN,'') = '' | Lists all chemicals on a location and all its sub locations that are classified as Dangerous Goods. | Delete | Edit |
| 211 | Chemistry Agent Organization | ReportsPage | 1100 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | select
h.Id,
(select Item from SplitString(pp.Path, '|') where Ordinal = 0) as Department,
(select Item from SplitString(pp.Path, '|') where Ordinal = 1) as Section,
(select Item from SplitString(pp.Path, '|') where Ordinal = 2) as Subsection,
ca.AgentName,
ca.AgentEmail,
h.LeaderName,
h.LeaderEmail
FROM
T_Hierarchys h
inner join HierarchyPathsEx('|') pp on pp.id = h.id
AND pp.PathIDs LIKE CASE WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%' ELSE '%' END
left outer join T_Hierarchy_Chemistry_Agents hca on hca.t_hierarchy_Id = h.id
left outer join T_ChemistryAgents ca on ca.Id = hca.t_chemistryAgentId
order by
pp.Path,
ca.AgentName | Lists all chemistry agents. | Delete | Edit |
| 212 | Chemistry Agent Organization for the place of use | ReportsPage | 1100 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | select
h.Id,
(select Item from SplitString(pp.Path, '|') where Ordinal = 0) as Department,
(select Item from SplitString(pp.Path, '|') where Ordinal = 1) as Section,
(select Item from SplitString(pp.Path, '|') where Ordinal = 2) as Subsection,
ca.AgentName,
ca.AgentEmail,
h.LeaderName,
h.LeaderEmail
FROM
T_Hierarchys h
inner join HierarchyPathsEx('|') pp on pp.id = h.id
left outer join T_Hierarchy_Chemistry_Agents hca on hca.t_hierarchy_Id = h.id
left outer join T_ChemistryAgents ca on ca.Id = hca.t_chemistryAgentId
where h.id = @p0
order by
pp.Path,
ca.AgentName | Lists all chemistry agents. | Delete | Edit |
| 217 | Processes APV | ReportsPage | 1200 | | using System; public class $$ContainerClassName$${$$FieldDef$$} | System.dll | public $$ColumnType$$ $$ColumnName$$ { get; set; } | | | declare @aoLang as varchar(2) = case lower(@p1) when 'da-dk' then 'dk' else 'en' end;
select
isnull(ppp.Path,'') as Hierarchy,
case @aoLang
when 'dk' then case isnull(pcs.DKName,'') when '' then pcs.CanonicalName + '*' else pcs.DKName end -- Asterisk indicates fallback because the requested language wasn't found.
else case isnull(pcs.ENName,'') when '' then pcs.CanonicalName + '*' else pcs.ENName end -- Asterisk indicates fallback because the requested language wasn't found.
end as Process,
pcs.DKName as ProcessNameDK,
pcs.ENName as ProcessNameEN,
pcs.ID as ProcessId,
ph.HierarchyID as HierarchyId,
case pcs.Status
when 0 then case when @aoLang = 'dk' then 'Oprettet' else 'Created' end
when 1 then case when @aoLang = 'dk' then 'Frigivet' else 'Released' end
when 2 then case when @aoLang = 'dk' then 'Slettet' else 'Deleted' end
end as Status
from T_APV_Processes pcs
left outer join T_APV_Process_Hierachies ph on ph.APVProcessID = pcs.ID
JOIN dbo.HierarchyPathsEx(' / ') ppp ON ppp.Id = ph.HierarchyID AND ppp.PathIDs LIKE CASE WHEN ISNULL(@p0,0) >0 THEN '%$' + CAST(@p0 AS NVARCHAR(8)) + '$%' ELSE '%' END
order by
ppp.Path, pcs.DKName, pcs.ENName, pcs.CanonicalName | Shows a list of Process APV's and the hierarchies they are attached to.
Remark: Names with * (Asterisk) at the end, indicates that the canonical name was used due to lag of tranlation. | Delete | Edit |