Adrstockreport

Report ADRStockReport

• Filename: ADRStockReport
• Title: ADR
• Sample: Unit222

Synopsis

ADR stock report

SQL

SELECT
   A.ARTCODE, A.LOCATION, A.STOCKCODE,
   ADR_CLASS+'   '+ADR_PACKAGE as ADR_GROUP,
   ADR_UN, ADR_NAME, ADR_CLASS, ADR_PACKAGE,
   ADR_CLASSIFICATION,
   ADR_LABEL,
   sum(QTYIN-isnull(QTYOUT, 0)) AS QTY,
   isnull(B.DangerousWeight, 0) as DangerousWeight,
   sum(QTYIN-isnull(QTYOUT, 0))*isnull(B.DangerousWeight, 0) as TDangerousWeight
 FROM
   STOCTOTAL A,
   INNER JOIN ARTICLE B ON (A.ARTCODE=B.NAME)
 where
   (isnull(ADR_CLASS, '')<>'')
   AND (QTYIN-isnull(QTYOUT, 0)>0)
 GROUP BY
   A.ARTCODE, A.LOCATION, A.STOCKCODE,
    ADR_UN, ADR_NAME, ADR_CLASS, ADR_PACKAGE, ADR_CLASSIFICATION, ADR_LABEL, DangerousWeight
 HAVING
   sum(QTYIN-isnull(QTYOUT, 0))>0
 ORDER BY
   ADR_GROUP

new sql

SELECT
   B.aCode as ArtCode,
   L.lName as LOCATION,
   A.idStockCode,
   B.aADRClass+'   '+B.aADRPACKAGE as ADR_GROUP,
   B.aADRUniqueName as ADR_UN,
   B.aADRDescription as ADR_NAME,
   B.aADRClass as ADR_CLASS,
   B.aADRPACKAGE as ADR_PACKAGE,
   B.aADRClassification as ADR_CLASSIFICATION,
   B.aADRClass as ADR_LABEL,
   Sum(a.idQuantity) AS QTY,
   B.aADRDangerousWeight as DangerousWeight,
   Sum((a.idQuantity-a.QTYOUT) * B.aADRDangerousWeight) as TDangerousWeight
 FROM
   StocTOTAL A
   INNER JOIN ARTICLE B ON (a.idArticleFK=b.aID)
   inner join Location l on (a.idLocationFK=L.lID)
 where
   (B.aADRClass<>'')
   AND ((a.idQuantity-a.QTYOUT)>0)
 GROUP BY
   B.aCode, l.Lname, A.idStockCode,
   B.aADRUniqueName, B.aADRDescription, B.aADRClass, B.aADRPACKAGE, B.aADRClassification, B.aADRClass, B.aADRDangerousWeight
 HAVING
   sum((a.idQuantity-a.QTYOUT))>0
 ORDER BY
   ADR_GROUP

Parameters

None.

Accessible in forms

• StockReports