Die Rezeptur-Baumstruktur auflösen

asu
Autor
Offline
Moderator

Mehr

14 Jahre 7 Monate her - 14 Jahre 7 Monate her #781 von asu

Die Rezeptur-Baumstruktur auflösen wurde erstellt von asu

Mit Hilfe von Datenbankabfragen ( ich gebe es zu, es sind etwas kompliziertere Abfragen

) kann man in Orgasoft NT natürlich auch weitere Anforderungen erfüllen. Hier zwei Beispiele und die dazu passenden Abfragen. Die Abfragen sind quasi die Basis für alle weiteren "Anforderungen" die danach kommen, mit Text und Preisen und Lieferanten, was auch immer damit dann gemacht werden soll. Hier im ScreenShot sehen Sie an oberster Stelle die Rezeptur/Stückliste, wie Sie sie von Orgasoft NT kennen. Direkt darunter eine Abfrage, welche die komplette Rezeptur strukturiert mit Hierarchie darstellt. Darunter wieder die gleiche Rezeptur, nur dieses mal ohne den eigentlichen Artikel, also nur die Bestandteile, bis runter in die letzte Stufe.

Abfrage 1:

Code:

SET NOCOUNT OFF ;
WITH    RecursionCTE ( UArtikelNr, UEinheit, UFarbe, UGrösse, ArtikelNr, Einheit, Farbe, Grösse, Menge, Row, Hierarchy )
          AS ( SELECT   ArtikelNr AS UArtikelNr ,
                        Einheit AS UEinheit ,
                        Farbe AS UFarbe ,
                        Grösse AS UGrösse ,
                        Artikelnr ,
                        Einheit ,
                        Farbe ,
                        Grösse ,
                        CAST(0 AS MONEY) AS Menge ,
                        CAST(0 AS BIGINT) AS Row ,
                        CONVERT(VARCHAR(100), '') Hierarchy
               FROM     HandelsArtikel
               WHERE    ArtikelNr = '090004'
                        AND Einheit = 0
                        AND Farbe = 0
                        AND Grösse = 'NULL'
               UNION ALL
               SELECT   R1.UArtikelNr ,
                        R1.UEinheit ,
                        R1.UFarbe ,
                        R1.UGrösse ,
                        R1.Artikelnr ,
                        R1.Einheit ,
                        R1.Farbe ,
                        R1.Grösse ,
                        R1.Menge ,
                        ROW_NUMBER() OVER ( PARTITION BY R1.Artikelnr,
                                            R1.Einheit, R1.Farbe, R1.Grösse ORDER BY R1.Artikelnr, R1.Einheit, R1.Farbe, R1.Grösse, R1.UArtikelNr, R1.UEinheit, R1.UFarbe, R1.UGrösse ) AS Row ,
                        CASE WHEN DATALENGTH(R2.Hierarchy) > 0
                             THEN CONVERT(VARCHAR(100), R2.Hierarchy + '.'
                                  + CAST(ROW_NUMBER() OVER ( PARTITION BY +R1.Artikelnr,
                                                             R1.Einheit,
                                                             R1.Farbe,
                                                             R1.Grösse ORDER BY +R1.Artikelnr, R1.Einheit, R1.Farbe, R1.Grösse, R1.UArtikelNr, R1.UEinheit, R1.UFarbe, R1.UGrösse ) AS VARCHAR(10)))
                             ELSE CONVERT(VARCHAR(100), CAST(ROW_NUMBER() OVER ( PARTITION BY R1.ArtikelNr,
                                                              R1.Einheit,
                                                              R1.Farbe,
                                                              R1.Grösse ORDER BY R1.Artikelnr, R1.Einheit, R1.Farbe, R1.Grösse, R1.UArtikelNr, R1.UEinheit, R1.UFarbe, R1.UGrösse ) AS VARCHAR(10)))
                        END AS TOC
               FROM     Stückliste AS R1
                        JOIN RecursionCTE AS R2
                            ON R1.Artikelnr = R2.UArtikelNr
                               AND R1.Einheit = R2.UEinheit
                               AND R1.Farbe = R2.UFarbe
                               AND R1.Grösse = R2.UGrösse
             )
    SELECT  *
    FROM    RecursionCTE
    ORDER BY Hierarchy ASC

Abfrage 2:

Code:

WITH    Stl ( StartArtikelNr, StartEinheit, ArtikelNr, Einheit, Farbe, Grösse, UArtikelNr, UEinheit, UFarbe, UGrösse, Menge, ArtLevel )
          AS ( SELECT   ArtikelNr AS StartArtikelNr ,
                        Einheit AS StartEinheit ,
                        ArtikelNr ,
                        Einheit ,
                        Farbe ,
                        Grösse ,
                        UArtikelNr ,
                        UEinheit ,
                        UFarbe ,
                        UGrösse ,
                        Menge ,
                        0 AS ArtLevel
               FROM     dbo.Stückliste
               UNION ALL
               SELECT   Stl_2.StartArtikelNr ,
                        Stl_2.StartEinheit ,
                        UnterArtikel.ArtikelNr AS Expr1 ,
                        UnterArtikel.Einheit AS Expr2 ,
                        UnterArtikel.Farbe ,
                        UnterArtikel.Grösse ,
                        UnterArtikel.UArtikelNr ,
                        UnterArtikel.UEinheit ,
                        UnterArtikel.UFarbe ,
                        UnterArtikel.UGrösse ,
                        UnterArtikel.Menge * Stl_2.Menge AS Expr4 ,
                        Stl_2.ArtLevel + 1 AS Expr3
               FROM     dbo.Stückliste AS UnterArtikel
                        INNER JOIN Stl AS Stl_2
                            ON UnterArtikel.ArtikelNr = Stl_2.UArtikelNr
                               AND UnterArtikel.Einheit = Stl_2.UEinheit
             )
    SELECT  StartArtikelNr ,
            StartEinheit ,
            ArtikelNr ,
            Einheit ,
            Farbe ,
            Grösse ,
            UArtikelNr ,
            UEinheit ,
            UFarbe ,
            UGrösse ,
            Menge ,
            ArtLevel
    FROM    Stl AS Stl_1

"Wer Rechtschreibfehler findet, darf sie behalten."

Anhänge:

bueromaus
Offline
Neues Mitglied

Mehr

14 Jahre 7 Monate her - 14 Jahre 7 Monate her #176 von bueromaus

bueromaus antwortete auf Re: Die Rezeptur-Baumstruktur auflösen

Hallöle asu....und die anderen natürlich auch

das ist ja SPITZE....ich habe mal aus beiden Rezeptur-Einträgen für mich was zusammen "gebastelt".

Ansatz war die Abfrage die die Rezeptur komplett in einer Abfrage darstellt. Ich wollte aber dann nur eine Liste der Artikel, die am "Ende" stehen, also ganz rechts in der Tree-View. Wie soll ich es ausdrücken, da wo es halt nicht mehr weiter, sprich alles was dann keine Stückliste bzw. Rezeptur mehr ist. Und da Salz und Pfeffer ja in vielen Zwischenschritten vorkommen, musste ich diese übrig gebliebenen Positionen noch summieren. Dann habe ich aus dem Rezepturdruck die Funktion mit dem AskString$ ("Anzahl",False,"1",3) noch mit aufgegriffen. Rausgekommen ist eine Lager-Pickliste für mich. Ich gebe vor dem Druck die Menge ein, welche ich kochen/backen/richten muss und habe eine Liste mit den Artikeln, die ich dazu als Grund-Zutaten benötige. Bei uns passt das hervorragend.

Und da es ja jetzt üblich ist, die Codes hier zu veröffentlichen, bitte schön, meine modifizierte UserQuery

ohne Gewähr

Grüssle
eure bueromaus

Code:

Create Procedure UserQueryLagerPickliste 
@100_100 varchar(20), @100_101 smallint
as
SET NOCOUNT OFF ;
WITH    RecursionCTE ( UArtikelNr, UEinheit, UFarbe, UGrösse, ArtikelNr, Einheit, Farbe, Grösse, Menge, Row, Hierarchy )
          AS ( SELECT   ArtikelNr AS UArtikelNr ,
                        Einheit AS UEinheit ,
                        Farbe AS UFarbe ,
                        Grösse AS UGrösse ,
                        Artikelnr ,
                        Einheit ,
                        Farbe ,
                        Grösse ,
                        CAST(0 AS MONEY) AS Menge ,
                        CAST(0 AS BIGINT) AS Row ,
                        CONVERT(VARCHAR(100), '') Hierarchy
               FROM     HandelsArtikel
               WHERE    ArtikelNr = @100_100
                        AND Einheit = @100_101
                        AND Farbe = 0
                        AND Grösse = 'NULL'
               UNION ALL
               SELECT   R1.UArtikelNr ,
                        R1.UEinheit ,
                        R1.UFarbe ,
                        R1.UGrösse ,
                        R1.Artikelnr ,
                        R1.Einheit ,
                        R1.Farbe ,
                        R1.Grösse ,
                        R1.Menge ,
                        ROW_NUMBER() OVER ( PARTITION BY R1.Artikelnr,
                                            R1.Einheit, R1.Farbe, R1.Grösse ORDER BY R1.Artikelnr, R1.Einheit, R1.Farbe, R1.Grösse, R1.UArtikelNr, R1.UEinheit, R1.UFarbe, R1.UGrösse ) AS Row ,
                        CASE WHEN DATALENGTH(R2.Hierarchy) > 0
                             THEN CONVERT(VARCHAR(100), R2.Hierarchy + '.'
                                  + CAST(ROW_NUMBER() OVER ( PARTITION BY +R1.Artikelnr,
                                                             R1.Einheit,
                                                             R1.Farbe,
                                                             R1.Grösse ORDER BY +R1.Artikelnr, R1.Einheit, R1.Farbe, R1.Grösse, R1.UArtikelNr, R1.UEinheit, R1.UFarbe, R1.UGrösse ) AS VARCHAR(10)))
                             ELSE CONVERT(VARCHAR(100), CAST(ROW_NUMBER() OVER ( PARTITION BY R1.ArtikelNr,
                                                              R1.Einheit,
                                                              R1.Farbe,
                                                              R1.Grösse ORDER BY R1.Artikelnr, R1.Einheit, R1.Farbe, R1.Grösse, R1.UArtikelNr, R1.UEinheit, R1.UFarbe, R1.UGrösse ) AS VARCHAR(10)))
                        END AS TOC
               FROM     Stückliste AS R1
                        JOIN RecursionCTE AS R2
                            ON R1.Artikelnr = R2.UArtikelNr
                               AND R1.Einheit = R2.UEinheit
                               AND R1.Farbe = R2.UFarbe
                               AND R1.Grösse = R2.UGrösse
             )
    SELECT  RecursionCTE.UArtikelNr,Einheit.Kürzel,sum(Menge)as Menge, Artikel.Kurztext, Artikel.WGKürzel,Warengruppe.Bezeichnung as WGBezeichnung
    FROM    RecursionCTE inner join HandelsArtikel HA 
    on RecursionCTE.UArtikelNr = HA.ArtikelNr and RecursionCTE.UEinheit=ha.einheit 
    and RecursionCTE.UFarbe=HA.Farbe and RecursionCTE.UGrösse=HA.Grösse inner join Artikel on
    RecursionCTE.UArtikelNr = Artikel.ArtikelNr inner join Einheit on
    Einheit.Einheit=RecursionCTE.UEinheit inner join Warengruppe
    on Artikel.WGKürzel=Warengruppe.WGKürzel
    where HA.StücklisteJN=0
    group by UArtikelNr,Kurztext,Kürzel, Artikel.WGKürzel,Warengruppe.Bezeichnung
    ORDER BY WGKürzel

asu
Autor
Offline
Moderator

Mehr

14 Jahre 7 Monate her - 14 Jahre 7 Monate her #185 von asu

asu antwortete auf Re: Die Rezeptur-Baumstruktur auflösen

Hallo,

ein weiterer Tipp noch. Wenn Sie in der Ausgabe die ArtikelNr des ursprünglichen Rezepturartikels benötigen, dann können Sie diese ganz einfach in der Select-Anweisung mit
"Select @100_100 as ArtikelNr, ...." verwenden, da diese ja als Variable deklariert ist für die Anbindung ins Artikelmenü.

Brauchen Sie zusätzlich noch den Kurztext, so müssen Sie eine neue Variable zuerst deklarieren und können diese dann in gleicher Form verwenden.
"Select @100_100 as ArtikelNr, @100_002 as Kurztext ...."

Anwender mit ein klein wenig Erfahrung werden an dieser Stelle erkennen, dass es sich hierbei um den bekannten Parameter 100-002 Artikel.Kurztext handelt aus den Listendesigner.

Gruß

asu

"Wer Rechtschreibfehler findet, darf sie behalten."

asu
Autor
Offline
Moderator

Mehr

12 Jahre 11 Monate her - 11 Jahre 2 Monate her #555 von asu

asu antwortete auf Re: Die Rezeptur-Baumstruktur auflösen

Hallo zusammen,

eine weitere Variante, dieses mal ohne Preise, jedoch mit der kompletten Rezeptur-Struktur, wobei im Druck-Layout darauf wert gelegt wurde, die Baumstruktur darzustellen, was durch unterschiedliche Datenzeilen mit unterschiedlichen Layouts mit Hilfe des Werts "Level" gemacht werden kann. Die Möglichkeiten sind alle da.

Gruß
asu

Code:

CREATE Procedure UserQueryRezepturKüche

@100_100 varchar(20), @Artikel#HandelsArtikel#Einheit smallint
as
/*
declare @100_100  varchar(20)
declare  @Artikel#HandelsArtikel#Einheit smallint
set @100_100 ='090006'
set  @Artikel#HandelsArtikel#Einheit=0
*/
SET NOCOUNT OFF ;
WITH    RecursionCTE ( UArtikelNr, UEinheit, UFarbe, UGrösse, ArtikelNr, Einheit, Farbe, Grösse, Menge, Row, Hierarchy )
          AS ( SELECT   ArtikelNr AS UArtikelNr ,
                        Einheit AS UEinheit ,
                        Farbe AS UFarbe ,
                        Grösse AS UGrösse ,
                        Artikelnr ,
                        Einheit ,
                        Farbe ,
                        Grösse ,
                        CAST(0 AS MONEY) AS Menge ,
                        CAST(0 AS BIGINT) AS Row ,
                        CONVERT(VARCHAR(100), '') Hierarchy
               FROM     HandelsArtikel
               WHERE    ArtikelNr = @100_100 
                        AND Einheit =  @Artikel#HandelsArtikel#Einheit
                        AND Farbe = 0
                        AND Grösse = 'NULL'
               UNION ALL
               SELECT   R1.UArtikelNr ,
                        R1.UEinheit ,
                        R1.UFarbe ,
                        R1.UGrösse ,
                        R1.Artikelnr ,
                        R1.Einheit ,
                        R1.Farbe ,
                        R1.Grösse ,
                        R1.Menge ,
                        ROW_NUMBER() OVER ( PARTITION BY R1.Artikelnr,
                                            R1.Einheit, R1.Farbe, R1.Grösse ORDER BY R1.Artikelnr, R1.Einheit, R1.Farbe, R1.Grösse, R1.UArtikelNr, R1.UEinheit, R1.UFarbe, R1.UGrösse ) AS Row ,
                        CASE WHEN DATALENGTH(R2.Hierarchy) > 0
                             THEN CONVERT(VARCHAR(100), R2.Hierarchy + '.'
                                  + CAST(ROW_NUMBER() OVER ( PARTITION BY +R1.Artikelnr,
                                                             R1.Einheit,
                                                             R1.Farbe,
                                                             R1.Grösse ORDER BY +R1.Artikelnr, R1.Einheit, R1.Farbe, R1.Grösse, R1.UArtikelNr, R1.UEinheit, R1.UFarbe, R1.UGrösse ) AS VARCHAR(10)))
                             ELSE CONVERT(VARCHAR(100), CAST(ROW_NUMBER() OVER ( PARTITION BY R1.ArtikelNr,
                                                              R1.Einheit,
                                                              R1.Farbe,
                                                              R1.Grösse ORDER BY R1.Artikelnr, R1.Einheit, R1.Farbe, R1.Grösse, R1.UArtikelNr, R1.UEinheit, R1.UFarbe, R1.UGrösse ) AS VARCHAR(10)))
                        END AS TOC
               FROM     Stückliste AS R1
                        JOIN RecursionCTE AS R2
                            ON R1.Artikelnr = R2.UArtikelNr
                               AND R1.Einheit = R2.UEinheit
                               AND R1.Farbe = R2.UFarbe
                               AND R1.Grösse = R2.UGrösse
             )
    SELECT top 100 percent UArtikelNr,Einheit.Bezeichnung,Menge,case when Hierarchy='' then '0' else Hierarchy end as Hierarchy, Artikel.Kurztext, Herstellung, ArtikelBild.DateiName

    FROM    RecursionCTE
    INNER JOIN Artikel ON RecursionCTE.UArtikelNr=Artikel.ArtikelNr
      LEFT OUTER JOIN
                      --subselect für die Herstellungsanweisung
                      (SELECT ArtikelNr, Feldnummer, CONVERT(varchar(500), Memofeld) AS Herstellung FROM ArtikelHatMultiFeld WHERE (Feldnummer = 103))
                      as UserAHM103 ON @100_100    = UserAHM103.ArtikelNr
                      --Bild
                      LEFT OUTER JOIN
                      ArtikelBild ON @100_100   = ArtikelBild.ArtikelNr
                      INNER JOIN Einheit
                      ON Einheit.Einheit=UEinheit
                            order by convert(bigint,case when CHARINDEX('.',Hierarchy,1) <> 0 then LEFT(Hierarchy,CHARINDEX('.',Hierarchy,1)-1) else Hierarchy end), convert(bigint,replace(Hierarchy,'.',''))

Liste

"Wer Rechtschreibfehler findet, darf sie behalten."

Anhänge:

Letzte Änderung: 11 Jahre 2 Monate her von asu.

Signum Warenwirtschaftssysteme GmbH
Kasinostraße 2
DE-64293 Darmstadt

Tel: +49 (6151) 15 18 - 0
Fax: +49 (6151) 15 18 - 100
team@signum.info