309 lines
15 KiB
XML
309 lines
15 KiB
XML
<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="test.db" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="3270"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><table title="PARTS" custom_title="0" dock_id="1" table="4,5:mainPARTS"/><dock_state state="000000ff00000000fd00000001000000020000058b00000395fc0100000001fc000000000000058b0000012100fffffffa000000000100000002fb000000160064006f0063006b00420072006f00770073006500310100000000ffffffff0000012100fffffffb000000160064006f0063006b00420072006f00770073006500340000000000ffffffff00000000000000000000058b0000000000000004000000040000000800000008fc00000000"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="COMP" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort><column index="1" mode="0"/></sort><column_widths><column index="1" value="50"/><column index="2" value="79"/><column index="3" value="48"/><column index="4" value="90"/><column index="5" value="107"/><column index="6" value="48"/><column index="7" value="87"/><column index="8" value="52"/><column index="9" value="73"/><column index="10" value="76"/><column index="11" value="72"/><column index="12" value="63"/><column index="13" value="72"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="PARTS" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort><column index="6" mode="1"/></sort><column_widths><column index="1" value="78"/><column index="2" value="300"/><column index="3" value="39"/><column index="4" value="53"/><column index="5" value="64"/><column index="6" value="108"/><column index="7" value="107"/><column index="8" value="41"/><column index="9" value="97"/><column index="10" value="126"/><column index="12" value="112"/><column index="13" value="135"/><column index="14" value="54"/><column index="15" value="51"/><column index="16" value="218"/><column index="17" value="195"/><column index="18" value="73"/><column index="19" value="65"/><column index="20" value="76"/><column index="21" value="113"/><column index="22" value="76"/><column index="23" value="70"/><column index="24" value="102"/><column index="25" value="52"/></column_widths><filter_values><column index="4" value="M"/><column index="0" value="822066089"/></filter_values><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="RD_COMP" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort><column index="1" mode="0"/></sort><column_widths><column index="1" value="35"/><column index="2" value="78"/><column index="3" value="105"/><column index="4" value="78"/><column index="5" value="45"/><column index="6" value="105"/><column index="7" value="122"/></column_widths><filter_values><column index="6" value="5"/></filter_values><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="STKL_REGEL" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="35"/><column index="2" value="82"/><column index="3" value="134"/><column index="4" value="44"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="VAR_TEIL_BEDINGUNG_2" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort><column index="3" mode="0"/></sort><column_widths><column index="1" value="48"/><column index="2" value="140"/><column index="3" value="137"/><column index="4" value="102"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table></browse_table_settings></tab_browse><tab_sql><sql name="1_create_tmp_not_identical">DROP TABLE IF EXISTS TMP_CODES_CASE_12_OR_13;
|
|
CREATE TABLE TMP_CODES_CASE_12_OR_13 (KOMPTEILENR TEXT NOT NULL, PRIMARY KEY (KOMPTEILENR));
|
|
|
|
WITH combined_bom AS (
|
|
SELECT C.KOMPTEILENR, COALESCE(C.LAENGE, 0) AS LAENGE
|
|
FROM COMP C
|
|
WHERE C.UEBTEILENR = '000000372'
|
|
|
|
UNION ALL
|
|
|
|
SELECT ChildCode, Uebergabelaenge
|
|
FROM RD_COMP
|
|
WHERE ParentCode = '000000372'
|
|
)
|
|
|
|
INSERT INTO TMP_CODES_CASE_12_OR_13
|
|
SELECT DISTINCT KOMPTEILENR
|
|
FROM (
|
|
SELECT KOMPTEILENR, LAENGE, COUNT(*) AS ANZAHL
|
|
FROM combined_bom
|
|
GROUP BY KOMPTEILENR, LAENGE
|
|
)
|
|
WHERE ANZAHL = 1;
|
|
|
|
SELECT * FROM TMP_CODES_CASE_12_OR_13</sql><sql name="2_create_tmp_not_same_pos">DROP TABLE IF EXISTS TMP_CODES_CASE_13;
|
|
CREATE TABLE TMP_CODES_CASE_13 (KOMPTEILENR TEXT NOT NULL, PRIMARY KEY(KOMPTEILENR));
|
|
|
|
WITH combined_bom AS (
|
|
SELECT KOMPTEILENR, POSNR
|
|
FROM COMP
|
|
WHERE UEBTEILENR = '000000372'
|
|
AND KOMPTEILENR IN (SELECT * FROM TMP_CODES_CASE_12_OR_13)
|
|
|
|
UNION ALL
|
|
|
|
SELECT ChildCode, Positionsnummer
|
|
FROM RD_COMP
|
|
WHERE ParentCode = '000000372'
|
|
AND ChildCode IN (SELECT * FROM TMP_CODES_CASE_12_OR_13)
|
|
)
|
|
|
|
INSERT INTO TMP_CODES_CASE_13 (KOMPTEILENR)
|
|
SELECT DISTINCT KOMPTEILENR
|
|
FROM (
|
|
SELECT KOMPTEILENR, COUNT(*) AS ANZAHL
|
|
FROM combined_bom
|
|
GROUP BY KOMPTEILENR, POSNR
|
|
)
|
|
WHERE ANZAHL = 1;
|
|
|
|
SELECT * FROM TMP_CODES_CASE_13</sql><sql name="3_get_case_11_pos">DROP TABLE IF EXISTS TMP_POSNRS_CASE_11;
|
|
CREATE TABLE TMP_POSNRS_CASE_11 (UEBTEILENR TEXT NOT NULL,
|
|
POS_ALT INTEGER NOT NULL,
|
|
POS_NEU INTEGER NOT NULL,
|
|
PRIMARY KEY(POS_NEU));
|
|
|
|
INSERT INTO TMP_POSNRS_CASE_11 (UEBTEILENR, POS_ALT, POS_NEU)
|
|
|
|
SELECT C.UEBTEILENR, C.POSNR AS POS_ALT, RD.Positionsnummer AS POS_NEU
|
|
FROM RD_COMP RD
|
|
|
|
LEFT JOIN TMP_CODES_CASE_12_OR_13 TMP
|
|
ON RD.ChildCode = TMP.KOMPTEILENR
|
|
|
|
LEFT JOIN COMP C
|
|
ON RD.ParentCode = C.UEBTEILENR
|
|
AND RD.ChildCode = C.KOMPTEILENR
|
|
AND RD.Uebergabelaenge = COALESCE(C.LAENGE, 0)
|
|
|
|
WHERE RD.ParentCode = '000000372'
|
|
AND TMP.KOMPTEILENR IS NULL;
|
|
|
|
SELECT * FROM TMP_POSNRS_CASE_11</sql><sql name="4_get_case_12_pos">DROP TABLE IF EXISTS TMP_POSNRS_CASE_12;
|
|
CREATE TABLE TMP_POSNRS_CASE_12 (UEBTEILENR TEXT NOT NULL,
|
|
POS_ALT INTEGER NOT NULL,
|
|
POS_NEU INTEGER NOT NULL,
|
|
PRIMARY KEY(POS_NEU));
|
|
|
|
INSERT INTO TMP_POSNRS_CASE_12 (UEBTEILENR, POS_ALT, POS_NEU)
|
|
|
|
SELECT C.UEBTEILENR, C.POSNR AS POS_ALT, RD.Positionsnummer AS POS_NEU
|
|
FROM RD_COMP RD
|
|
|
|
LEFT JOIN TMP_CODES_CASE_13 TMP
|
|
ON RD.ChildCode = TMP.KOMPTEILENR
|
|
|
|
LEFT JOIN COMP C
|
|
ON RD.ParentCode = C.UEBTEILENR
|
|
AND RD.ChildCode = C.KOMPTEILENR
|
|
AND RD.Positionsnummer = C.POSNR
|
|
|
|
WHERE RD.ParentCode = '000000372'
|
|
AND TMP.KOMPTEILENR IS NULL
|
|
AND RD.Positionsnummer NOT IN (SELECT POSNR FROM TMP_POSNRS_CASE_11);
|
|
|
|
SELECT * FROM TMP_POSNRS_CASE_12;</sql><sql name="5_get_case_131_pos">DROP TABLE IF EXISTS TMP_POSNRS_CASE_131;
|
|
CREATE TABLE TMP_POSNRS_CASE_131 (POSNR INTEGER NOT NULL, PRIMARY KEY(POSNR));
|
|
|
|
INSERT INTO TMP_POSNRS_CASE_131
|
|
|
|
SELECT RD.Positionsnummer AS POSNR
|
|
FROM RD_COMP RD
|
|
|
|
LEFT JOIN COMP C
|
|
ON RD.ParentCode = C.UEBTEILENR
|
|
AND RD.ChildCode = C.KOMPTEILENR
|
|
AND RD.Uebergabelaenge = COALESCE(C.LAENGE, 0)
|
|
|
|
WHERE RD.ParentCode = '000000372'
|
|
AND RD.ChildCode IN (SELECT * FROM TMP_CODES_CASE_13)
|
|
AND C.KOMPTEILENR IS NULL;
|
|
|
|
SELECT * FROM TMP_POSNRS_CASE_131;</sql><sql name="6_get_case_132_pos">DROP TABLE IF EXISTS TMP_POSNRS_CASE_132;
|
|
CREATE TABLE TMP_POSNRS_CASE_132 (UEBTEILENR TEXT NOT NULL,
|
|
POS_ALT INTEGER NOT NULL,
|
|
POS_NEU INTEGER NOT NULL,
|
|
PRIMARY KEY(POS_NEU));
|
|
|
|
INSERT INTO TMP_POSNRS_CASE_132 (UEBTEILENR, POS_ALT, POS_NEU)
|
|
|
|
SELECT C.UEBTEILENR, C.POSNR AS POS_ALT, RD.Positionsnummer AS POS_NEU
|
|
FROM RD_COMP RD
|
|
|
|
INNER JOIN COMP C
|
|
ON RD.ParentCode = C.UEBTEILENR
|
|
AND RD.ChildCode = C.KOMPTEILENR
|
|
AND RD.Uebergabelaenge = COALESCE(C.LAENGE, 0)
|
|
|
|
WHERE RD.ParentCode = '000000372'
|
|
AND RD.ChildCode IN (SELECT * FROM TMP_CODES_CASE_13);
|
|
|
|
SELECT * FROM TMP_POSNRS_CASE_132;</sql><sql name="7_get_ids_to_delete">WITH COMBINED_UPDATE_DATA AS (
|
|
SELECT * FROM TMP_POSNRS_CASE_11
|
|
UNION
|
|
SELECT * FROM TMP_POSNRS_CASE_12
|
|
UNION
|
|
SELECT * FROM TMP_POSNRS_CASE_132
|
|
)
|
|
--INSERT INTO TMP_IDS_TO_DELETE
|
|
|
|
SELECT C.ROWID AS COMP_ROWID, B.ROWID AS BED_ROWID, R.ROWID AS REG_ROWID
|
|
FROM COMP C
|
|
|
|
LEFT JOIN COMBINED_UPDATE_DATA CUD
|
|
ON C.UEBTEILENR = CUD.UEBTEILENR
|
|
AND C.POSNR = CUD.POS_ALT
|
|
|
|
LEFT JOIN VAR_TEIL_BEDINGUNG_2 B
|
|
ON C.UEBTEILENR = B.UEBTEILENR
|
|
AND C.POSNR = B.POSNR
|
|
|
|
LEFT JOIN STKL_REGEL R
|
|
ON C.UEBTEILENR = R.UEBTEILENR
|
|
AND C.POSNR = R.POSNR
|
|
|
|
WHERE C.UEBTEILENR = '000000372'
|
|
AND CUD.UEBTEILENR IS NULL</sql><sql name="get_update_data">DROP TABLE IF EXISTS TMP_UPDATE_DATA;
|
|
CREATE TABLE TMP_UPDATE_DATA (ROWID INTEGER NOT NULL,
|
|
POS_ALT INTEGER NOT NULL,
|
|
POS_NEU INTEGER NOT NULL,
|
|
ABW_ANZAHL INTEGER,
|
|
MENGE REAL,
|
|
LAENGE REAL,
|
|
ZUSCHNITT REAL,
|
|
DISPO_AKTIV_JN TEXT,
|
|
RUESTCODE TEXT,
|
|
BED_ROWID INTEGER,
|
|
REG_ROWID INTEGER,
|
|
UEB_LEISTEN_NR INTEGER,
|
|
LFD_NR INTEGER);
|
|
|
|
WITH ALL_POSNRS_TO_UPDATE AS (
|
|
SELECT * FROM TMP_POSNRS_CASE_11
|
|
UNION
|
|
SELECT * FROM TMP_POSNRS_CASE_12
|
|
UNION
|
|
SELECT * FROM TMP_POSNRS_CASE_132
|
|
)
|
|
INSERT INTO TMP_UPDATE_DATA (ROWID, POS_ALT, POS_NEU, ABW_ANZAHL, MENGE, LAENGE, ZUSCHNITT, DISPO_AKTIV_JN, RUESTCODE, BED_ROWID, REG_ROWID, UEB_LEISTEN_NR, LFD_NR)
|
|
|
|
SELECT C.ROWID,
|
|
P.POS_ALT,
|
|
RD.Positionsnummer AS POS_NEU,
|
|
|
|
-- Bestimmt Wert für Spalte 'ABW_ANZAHL': NULL, wenn Pos. nicht längenabhängig, sonst Wert 'Menge' aus CSV
|
|
CASE
|
|
WHEN RD.Uebergabelaenge = 0 THEN NULL
|
|
ELSE RD.Menge
|
|
END AS ABW_ANZAHL,
|
|
|
|
-- Bestimmt Wert für Spalte 'MENGE': Berechnung Dezimalwert, wenn Pos. längenabhängig, sonst Wert 'Menge' aus CSV
|
|
CASE
|
|
WHEN RD.Uebergabelaenge = 0 THEN RD.Menge
|
|
ELSE RD.Menge * (RD.Uebergabelaenge + COALESCE(C.ZUSCHNITT, 0)) / 1000
|
|
END AS MENGE,
|
|
|
|
-- Bestimmt Wert für Spalte 'LAENGE': NULL, wenn Pos. nicht längenabhängig, sonst Wert 'Uebergabelaenge' aus CSV
|
|
CASE
|
|
WHEN RD.Uebergabelaenge = 0 THEN NULL
|
|
ELSE RD.Uebergabelaenge
|
|
END AS LAENGE,
|
|
|
|
C.ZUSCHNITT,
|
|
C.DISPO_AKTIV_JN,
|
|
C.RUESTCODE,
|
|
B.ROWID,
|
|
R.ROWID,
|
|
B.UEB_LEISTEN_NR,
|
|
R.LFD_NR
|
|
|
|
FROM ALL_POSNRS_TO_UPDATE P
|
|
INNER JOIN COMP C
|
|
ON P.UEBTEILENR = C.UEBTEILENR
|
|
AND P.POS_ALT = C.POSNR
|
|
|
|
INNER JOIN RD_COMP RD
|
|
ON P.UEBTEILENR = RD.ParentCode
|
|
AND P.POS_NEU = RD.Positionsnummer
|
|
|
|
LEFT JOIN VAR_TEIL_BEDINGUNG_2 B
|
|
ON P.UEBTEILENR = B.UEBTEILENR
|
|
AND P.POS_ALT = B.POSNR
|
|
|
|
LEFT JOIN STKL_REGEL R
|
|
ON P.UEBTEILENR = R.UEBTEILENR
|
|
AND P.POS_ALT = R.POSNR;
|
|
|
|
SELECT * FROM TMP_UPDATE_DATA</sql><sql name="TOS-Workflowmeldung">SELECT C.POSNR, C.KOMPTEILENR
|
|
|
|
|
|
|
|
FROM COMP C
|
|
|
|
LEFT JOIN VAR_TEIL_BEDINGUNG_2 B
|
|
|
|
ON C.UEBTEILENR = B.UEBTEILENR
|
|
|
|
AND C.POSNR = B.POSNR
|
|
|
|
|
|
|
|
LEFT JOIN STKL_REGEL R
|
|
|
|
ON C.UEBTEILENR = R.UEBTEILENR
|
|
|
|
AND C.POSNR = R.POSNR
|
|
|
|
|
|
|
|
WHERE C.UEBTEILENR = '000000382'
|
|
|
|
AND (
|
|
|
|
B.UEB_LEISTEN_NR IS NOT NULL
|
|
|
|
OR R.LFD_NR IS NOT NULL
|
|
|
|
)
|
|
|
|
|
|
|
|
ORDER BY C.POSNR</sql><sql name="AV-Workflowmeldungen*">--längenabhängige Teilenummern herausfinden, die in beiden Stücklisten vorliegen
|
|
|
|
WITH laengenabh_codes_in_both_boms AS (
|
|
|
|
SELECT DISTINCT ChildCode
|
|
|
|
FROM RD_COMP
|
|
|
|
WHERE ParentCode = '000000382'
|
|
|
|
AND ChildCode in (
|
|
|
|
SELECT DISTINCT KOMPTEILENR
|
|
|
|
FROM COMP
|
|
|
|
WHERE UEBTEILENR = '000000382'
|
|
|
|
AND COALESCE(LAENGE, 0) != 0
|
|
|
|
)
|
|
|
|
)
|
|
|
|
|
|
|
|
SELECT C.POSNR, C.KOMPTEILENR
|
|
|
|
FROM COMP C
|
|
|
|
|
|
|
|
LEFT JOIN RD_COMP RD
|
|
|
|
ON C.UEBTEILENR = RD.ParentCode
|
|
|
|
AND C.KOMPTEILENR = RD.ChildCode
|
|
|
|
AND COALESCE(C.LAENGE, 0) = RD.Uebergabelaenge
|
|
|
|
|
|
|
|
INNER JOIN PARTS P
|
|
|
|
ON C.KOMPTEILENR = P.TEILENR
|
|
|
|
|
|
|
|
WHERE C.UEBTEILENR = '000000382'
|
|
|
|
AND C.KOMPTEILENR IN laengenabh_codes_in_both_boms -- Nur längenabhängige Teilenummern berücksichtigen, die in beiden Stücklisten vorliegen
|
|
|
|
AND RD.ParentCode IS NULL -- Nur Positionen berücksichtigen, deren Länge ausschließlich in der alten Stückliste vorliegt
|
|
|
|
AND P.INFO2_JN = "N" -- Nur Positionen berücksichtigen, deren zugehörige Teilenummer keinen Haken bei "Halbzeug ohne Sägeliste" im Teilestamm aufweist
|
|
|
|
AND ( -- Nur Positionen berücksichtigen, auf die min. eines der nachfolgenden Kriterien zutrifft:
|
|
|
|
C.ZUSCHNITT IS NOT NULL -- Zuschnittswert liegt vor
|
|
|
|
OR C.RUESTCODE != P.RUESTCODE -- Rüstcode der Position in der Stammstückliste ist abweichend zum Rüstcode der zugehörigen Teilenummer im Teilestamm
|
|
|
|
OR C.DISPO_AKTIV_JN = "N" -- Stücklistenpositionen besitzt keinen Haken in der Spalte "Dispoaktiv"
|
|
|
|
)
|
|
|
|
|
|
|
|
ORDER BY C.POSNR</sql><sql name="SQL 10">SELECT C.UEBTEILENR, C.POSNR, C.KOMPTEILENR, C.DISPO_AKTIV_JN, C.ZUSCHNITT, C.RUESTCODE, P.INFO2_JN, P.TEILENR, P.RUESTCODE AS P_RUESTCODE
|
|
|
|
FROM COMP C
|
|
|
|
INNER JOIN PARTS P
|
|
|
|
ON C.KOMPTEILENR = P.TEILENR
|
|
|
|
|
|
|
|
INNER JOIN RD_COMP RD
|
|
|
|
ON C.UEBTEILENR = RD.ParentCode
|
|
|
|
AND C.KOMPTEILENR = RD.ChildCode
|
|
|
|
|
|
|
|
WHERE C.UEBTEILENR = '000000369'
|
|
|
|
AND P.INFO2_JN = 'N'
|
|
|
|
AND
|
|
|
|
(
|
|
|
|
C.ZUSCHNITT IS NOT NULL
|
|
|
|
OR C.RUESTCODE != P.RUESTCODE
|
|
|
|
OR C.DISPO_AKTIV_JN = "N"
|
|
|
|
)
|
|
|
|
|
|
|
|
GROUP BY C.POSNR</sql><sql name="SQL 12">SELECT *
|
|
|
|
FROM COMP C
|
|
|
|
LEFT JOIN RD_COMP RD
|
|
|
|
ON C.UEBTEILENR = RD.ParentCode
|
|
|
|
AND C.KOMPTEILENR = RD.ChildCode
|
|
|
|
AND COALESCE(C.LAENGE, 0) = RD.Uebergabelaenge
|
|
|
|
|
|
|
|
WHERE C.UEBTEILENR = '000000371'
|
|
|
|
AND C.LAENGE IS NOT NULL -- Wenn es sich um eine längenabhängige Teilenummer handelt
|
|
|
|
AND RD.ParentCode IS NULL -- UND nicht alle Längenvarianten aus der alten Stückliste in der neuen Stückliste vorliegen
|
|
|
|
|
|
|
|
</sql><current_tab id="9"/></tab_sql></sqlb_project>
|