In Bearbeitung Bitteschön: SQL Skripte um Unstimmigkeiten in der DB zu finden und zu editieren (Amazon-Import, VCS und MwSt)

[tierfreund]

Bearbeitung durch Mod: Bitte beachtet, dass Eingriffe in die Datenbank ausschließlich auf eigene Gefahr erfolgen, da es hier immer zu unerwünschten Effekten kommen kann!

Da ich die Daten aus der Wawi für die Buchhaltung verwende, müssen diese auch stimmen. Leider geht bei mir beim Amazonimport (VCS) hier und da etwas schief.

Ich habe diverse Skripte geschrieben, welche die Daten in der DB mit den Amazon-Bestell- und Rechnungs-Berichten vergleichen (welche ja auch in der DB gespeichert sind) und die Unstimmigkeiten finden und bei Bedarf auch gleich "reparieren".

Die "Reparatur" erfolgt ausdrücklich auf eigene Gefahr. Ich bin nicht dafür verantwortlich, wenn Ihr euch die DB zerschießt. Für manche "Reparaturen" müsstet ihr sogar einen Trigger deaktivieren, welcher die Manipulation der Bestellpositionen verhindert, was ein ziemlicher Hack ist. Wenn ihr nicht wisst wie das geht, dann solltet ihr das auch bleiben lassen. Hierbei riskiert ihr Inkonsistenzen in der DB. Das ganze sollte besser über die SPs gelöst werde (Danke
Manuel Pietzsch)

Besser als die 'Reparatur' auf eigene Faust ist das gewissenenhafte manuelle Abklopfen der gefundenen "Fehlstellen" und bei verifizierten Fehlern den Support zu kontaktieren, damit JTL weiß wo etwas schiefgeht und die Software updaten kann.

Ich würde mich über Rückmeldungen freuen, ob Ihr auch Fehler in euren Datenbanken finden konntet oder bei euch alles korrekt ist.

Getestet auf Wawi-Version 1.5.25

-- Bestellungen mit mehreren MwSt-Sätzen anzeigen (Kein Fehler, aber bei Amazon-Importen sollte das nicht passieren)
select *
from tbestellpos t1
JOIN (SELECT tBestellung_kBestellung, MAX(fMwSt) as mwstmax, MIN(fMwSt) as mwstmin FROM tbestellpos WHERE fVKPreis <> 0 GROUP BY tBestellung_kBestellung) t2
ON t1.tBestellung_kBestellung = t2.tBestellung_kBestellung
JOIN tBestellung t3
ON t1.tBestellung_kBestellung = t3.kBestellung
and mwstmin <> mwstmax

-- Amazon-Rechnungen mit fehlerhafter Steuer (0%) anzeigen
SELECT OUR_PRICETaxExclusiveSellingPrice + OUR_PRICETaxExclusiveSellingPrice * [TaxRate] , OUR_PRICETaxInclusiveSellingPrice,*
FROM pf_amazon_vcs_history
WHERE
((OUR_PRICETaxInclusiveSellingPrice = OUR_PRICETaxExclusiveSellingPrice AND OUR_PRICETaxInclusiveSellingPrice !=0)
OR ([SHIPPINGTaxInclusiveSellingPrice] = [SHIPPINGTaxExclusiveSellingPrice] AND [SHIPPINGTaxInclusiveSellingPrice] !=0)
OR ([OUR_PRICETaxInclusivePromoAmount] = [OUR_PRICETaxExclusivePromoAmount] AND [OUR_PRICETaxInclusivePromoAmount] !=0)
OR ([SHIPPINGTaxInclusivePromoAmount] = [SHIPPINGTaxExclusivePromoAmount] AND [SHIPPINGTaxInclusivePromoAmount] !=0)
OR [TaxRate] = 0
OR (OUR_PRICETaxExclusiveSellingPrice + OUR_PRICETaxExclusiveSellingPrice * [TaxRate] - OUR_PRICETaxInclusiveSellingPrice > 0.1)
)
AND ExportOutsideEU like 'false'
AND (LEN(BuyerTaxRegistration) < 5 OR BuyerTaxRegistration like 'DE%')

-- Fehlende Ausfuhrlieferung bei Amazon-Bestellungen  finden
select *
FROM tBestellung
JOIN trechnung
ON tBestellung.kBestellung = trechnung.tBestellung_kBestellung
Join pf_amazon_vcs_history
on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where ExportOutsideEU like 'true'
and Taxrate like '0.00%'
and nUStFrei != 20

-- Fehlende IGL bei Amazon-Bestellungen  finden
select *
FROM
tBestellung
JOIN trechnung
ON tBestellung.kBestellung = trechnung.tBestellung_kBestellung
Join pf_amazon_vcs_history
on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where LEN(BuyerTaxRegistration) >5 and BuyerTaxRegistration not like 'DE%'
and ExportOutsideEU like 'false'
and Taxrate like '0.00%'
and nUStFrei != 10

-- Fehlerhaftes Amazon-Rechnungsdatum finden
select*
FROM tRechnung
Join pf_amazon_vcs_history
on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where dErstellt <> pf_amazon_vcs_history.ShipmentDate
and cRechnungsNr NOT LIKE 'DE-%'

-- Fehlerhafte Steuer bei Amazon-Versandzuschlag finden
select *
from tbestellpos t1
JOIN (SELECT tBestellung_kBestellung, MAX(fMwSt) as mwstmax FROM tbestellpos GROUP BY tBestellung_kBestellung) t2
ON t1.tBestellung_kBestellung = t2.tBestellung_kBestellung
where cString like '%Versandzuschlag%'
and fMwSt < mwstmax
and fVKPreis <> 0

-- Fehlerhafte Mehrwertsteuer der Bestellpositionen (Amazon) finden
select *
FROM tbestellpos
Join trechnung on tbestellpos.tBestellung_kBestellung = trechnung.tBestellung_kBestellung
Join tBestellung on tbestellpos.tBestellung_kBestellung = tBestellung.kBestellung
Join pf_amazon_vcs_history on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where tbestellpos.fMwSt <> TaxRate*100
and fVKPreis <> 0


-- Fehlerhafte Mehrwertsteuer der Amazon Gutschrift finden
select*
FROM tgutschrift
Join pf_amazon_vcs_history
on tgutschrift.cGutschriftNr = pf_amazon_vcs_history.VATInvoiceNumber
where tGutschrift.fMwSt <> TaxRate*100

-- Fehlerhafte Mehrwertsteuer der Amazon-Gutschriftspositionen finden
select *
FROM tGutschriftPos
Join tgutschrift
on tGutschriftPos.tGutschrift_kGutschrift = tgutschrift.kGutschrift
Join pf_amazon_vcs_history
on tgutschrift.cGutschriftNr = pf_amazon_vcs_history.VATInvoiceNumber
where tGutschriftPos.fMwSt <> TaxRate*100


-- Ausfuhrlieferung für Amazon-Bestellungen  setzen
update tBestellung
set nUStFrei = 20
FROM tBestellung
JOIN trechnung
ON tBestellung.kBestellung = trechnung.tBestellung_kBestellung
Join pf_amazon_vcs_history
on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where ExportOutsideEU like 'true'
and Taxrate like '0.00%'
and nUStFrei != 20

-- IGL setzen
update tBestellung
set nUStFrei = 10
FROM
tBestellung
JOIN trechnung
ON tBestellung.kBestellung = trechnung.tBestellung_kBestellung
Join pf_amazon_vcs_history
on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where LEN(BuyerTaxRegistration) >5 and BuyerTaxRegistration not like 'DE%'
and ExportOutsideEU like 'false'
and Taxrate like '0.00%'
and nUStFrei != 10

-- Amazon-Rechnungen auf das Amazon-Versanddatum datieren
update tRechnung
set dErstellt = pf_amazon_vcs_history.ShipmentDate
FROM tRechnung
Join pf_amazon_vcs_history
on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where dErstellt <> pf_amazon_vcs_history.ShipmentDate
and cRechnungsNr NOT LIKE 'DE-%'

-- Amazon-Versandzuschlag auf maximale Mehwertsteuer der Rechnung setzen
update tbestellpos
set fMwSt = mwstmax
from tbestellpos t1
JOIN (SELECT tBestellung_kBestellung, MAX(fMwSt) as mwstmax FROM tbestellpos GROUP BY tBestellung_kBestellung) t2
ON t1.tBestellung_kBestellung = t2.tBestellung_kBestellung
where cString like '%Versandzuschlag%'
and fMwSt < mwstmax
and fVKPreis <> 0

-- Mehrwertsteuer aller Bestellpositionen einer Rechnung gleich jener der Amazon Rechnung setzen
update tbestellpos
set tbestellpos.fMwSt = TaxRate*100
FROM tbestellpos
Join trechnung on tbestellpos.tBestellung_kBestellung = trechnung.tBestellung_kBestellung
Join tBestellung on tbestellpos.tBestellung_kBestellung = tBestellung.kBestellung
Join pf_amazon_vcs_history on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where tbestellpos.fMwSt <> TaxRate*100

-- Mehrwertsteuer der Gutschrift gleich der Amazon Gutschrift setzen
update tgutschrift
set fMwSt = TaxRate*100
FROM tgutschrift
Join pf_amazon_vcs_history
on tgutschrift.cGutschriftNr = pf_amazon_vcs_history.VATInvoiceNumber
where tGutschrift.fMwSt <> TaxRate*100

-- Mehrwertsteuer der Gutschriftspositionen gleich der Amazon Gutschrift setzen
update tGutschriftPos
set tGutschriftPos.fMwSt = TaxRate*100
FROM tGutschriftPos
Join tgutschrift
on tGutschriftPos.tGutschrift_kGutschrift = tgutschrift.kGutschrift
Join pf_amazon_vcs_history
on tgutschrift.cGutschriftNr = pf_amazon_vcs_history.VATInvoiceNumber
where tGutschriftPos.fMwSt <> TaxRate*100

 

[NETZdinge.de]

Bearbeitung durch Mod: Bitte beachtet, dass Eingriffe in die Datenbank ausschließlich auf eigene Gefahr erfolgen, da es hier immer zu unerwünschten Effekten kommen kann!

Da ich die Daten aus der Wawi für die Buchhaltung verwende, müssen diese auch stimmen. Leider geht offensichtlich beim Amazonimport hier und da etwas schief.

Ich habe diverse Skripte geschrieben, welche die Daten in der DB mit den Amazon-Bestell- und Rechnungs-Berichten vergleichen (welche ja auch in der DB gespeichert sind) und die Unstimmigkeiten finden und bei Bedarf auch gleich reparieren.

Die "Reparatur" erfolgt ausdrücklich auf eigene Gefahr. Ich bin nicht dafür Verantwortlich, wenn Ihr euch die DB zerschießt. Für manche "Reparaturen" müsstet ihr sogar einen Trigger deaktivieren, welcher die Manipulation der Bestellpositionen verhindert. Wenn ihr nicht wisst wie das geht, dann solltet ihr das auch bleiben lassen.

Ich würde mich über Rückmeldungen freuen, ob Ihr auch Feher in euren Datenbanken finden konntet:

-- Bestellungen mit mehreren MwSt-Sätzen anzeigen
select *
from tbestellpos t1
JOIN (SELECT tBestellung_kBestellung, MAX(fMwSt) as mwstmax, MIN(fMwSt) as mwstmin FROM tbestellpos WHERE fVKPreis <> 0 GROUP BY tBestellung_kBestellung) t2
ON t1.tBestellung_kBestellung = t2.tBestellung_kBestellung
JOIN tBestellung t3
ON t1.tBestellung_kBestellung = t3.kBestellung
and mwstmin <> mwstmax

-- Amazon-Rechnungen mit fehlerhafter Steuer (0%) anzeigen
SELECT OUR_PRICETaxExclusiveSellingPrice + OUR_PRICETaxExclusiveSellingPrice * [TaxRate] , OUR_PRICETaxInclusiveSellingPrice,*
FROM pf_amazon_vcs_history
WHERE
((OUR_PRICETaxInclusiveSellingPrice = OUR_PRICETaxExclusiveSellingPrice AND OUR_PRICETaxInclusiveSellingPrice !=0)
OR ([SHIPPINGTaxInclusiveSellingPrice] = [SHIPPINGTaxExclusiveSellingPrice] AND [SHIPPINGTaxInclusiveSellingPrice] !=0)
OR ([OUR_PRICETaxInclusivePromoAmount] = [OUR_PRICETaxExclusivePromoAmount] AND [OUR_PRICETaxInclusivePromoAmount] !=0)
OR ([SHIPPINGTaxInclusivePromoAmount] = [SHIPPINGTaxExclusivePromoAmount] AND [SHIPPINGTaxInclusivePromoAmount] !=0)
OR [TaxRate] = 0
OR (OUR_PRICETaxExclusiveSellingPrice + OUR_PRICETaxExclusiveSellingPrice * [TaxRate] - OUR_PRICETaxInclusiveSellingPrice > 0.1)
)
AND ExportOutsideEU like 'false'
AND (LEN(BuyerTaxRegistration) < 5 OR BuyerTaxRegistration like 'DE%')

-- Fehlende Ausfuhrlieferung bei Amazon-Bestellungen  finden
select *
FROM tBestellung
JOIN trechnung
ON tBestellung.kBestellung = trechnung.tBestellung_kBestellung
Join pf_amazon_vcs_history
on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where ExportOutsideEU like 'true'
and Taxrate like '0.00%'
and nUStFrei != 20

-- Fehlende IGL bei Amazon-Bestellungen  finden
select *
FROM
tBestellung
JOIN trechnung
ON tBestellung.kBestellung = trechnung.tBestellung_kBestellung
Join pf_amazon_vcs_history
on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where LEN(BuyerTaxRegistration) >5 and BuyerTaxRegistration not like 'DE%'
and ExportOutsideEU like 'false'
and Taxrate like '0.00%'
and nUStFrei != 10

-- Fehlerhaftes Amazon-Rechnungsdatum finden
select*
FROM tRechnung
Join pf_amazon_vcs_history
on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where dErstellt <> pf_amazon_vcs_history.ShipmentDate
and cRechnungsNr NOT LIKE 'DE-%'

-- Fehlerhafte Steuer bei Amazon-Versandzuschlag finden
select *
from tbestellpos t1
JOIN (SELECT tBestellung_kBestellung, MAX(fMwSt) as mwstmax FROM tbestellpos GROUP BY tBestellung_kBestellung) t2
ON t1.tBestellung_kBestellung = t2.tBestellung_kBestellung
where cString like '%Versandzuschlag%'
and fMwSt < mwstmax
and fVKPreis <> 0

-- Fehlerhafte Mehrwertsteuer der Bestellpositionen (Amazon) finden
select *
FROM tbestellpos
Join trechnung on tbestellpos.tBestellung_kBestellung = trechnung.tBestellung_kBestellung
Join tBestellung on tbestellpos.tBestellung_kBestellung = tBestellung.kBestellung
Join pf_amazon_vcs_history on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where tbestellpos.fMwSt <> TaxRate*100
and fVKPreis <> 0


-- Fehlerhafte Mehrwertsteuer der Amazon Gutschrift finden
select*
FROM tgutschrift
Join pf_amazon_vcs_history
on tgutschrift.cGutschriftNr = pf_amazon_vcs_history.VATInvoiceNumber
where tGutschrift.fMwSt <> TaxRate*100

-- Fehlerhafte Mehrwertsteuer der Amazon-Gutschriftspositionen finden
select *
FROM tGutschriftPos
Join tgutschrift
on tGutschriftPos.tGutschrift_kGutschrift = tgutschrift.kGutschrift
Join pf_amazon_vcs_history
on tgutschrift.cGutschriftNr = pf_amazon_vcs_history.VATInvoiceNumber
where tGutschriftPos.fMwSt <> TaxRate*100


-- Ausfuhrlieferung für Amazon-Bestellungen  setzen
update tBestellung
set nUStFrei = 20
FROM tBestellung
JOIN trechnung
ON tBestellung.kBestellung = trechnung.tBestellung_kBestellung
Join pf_amazon_vcs_history
on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where ExportOutsideEU like 'true'
and Taxrate like '0.00%'
and nUStFrei != 20

-- IGL setzen
update tBestellung
set nUStFrei = 10
FROM
tBestellung
JOIN trechnung
ON tBestellung.kBestellung = trechnung.tBestellung_kBestellung
Join pf_amazon_vcs_history
on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where LEN(BuyerTaxRegistration) >5 and BuyerTaxRegistration not like 'DE%'
and ExportOutsideEU like 'false'
and Taxrate like '0.00%'
and nUStFrei != 10

-- Amazon-Rechnungen auf das Amazon-Versanddatum datieren
update tRechnung
set dErstellt = pf_amazon_vcs_history.ShipmentDate
FROM tRechnung
Join pf_amazon_vcs_history
on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where dErstellt <> pf_amazon_vcs_history.ShipmentDate
and cRechnungsNr NOT LIKE 'DE-%'

-- Amazon-Versandzuschlag auf maximale Mehwertsteuer der Rechnung setzen
update tbestellpos
set fMwSt = mwstmax
from tbestellpos t1
JOIN (SELECT tBestellung_kBestellung, MAX(fMwSt) as mwstmax FROM tbestellpos GROUP BY tBestellung_kBestellung) t2
ON t1.tBestellung_kBestellung = t2.tBestellung_kBestellung
where cString like '%Versandzuschlag%'
and fMwSt < mwstmax
and fVKPreis <> 0

-- Mehrwertsteuer aller Bestellpositionen einer Rechnung gleich jener der Amazon Rechnung setzen
update tbestellpos
set tbestellpos.fMwSt = TaxRate*100
FROM tbestellpos
Join trechnung on tbestellpos.tBestellung_kBestellung = trechnung.tBestellung_kBestellung
Join tBestellung on tbestellpos.tBestellung_kBestellung = tBestellung.kBestellung
Join pf_amazon_vcs_history on trechnung.cRechnungsNr = pf_amazon_vcs_history.VATInvoiceNumber
where tbestellpos.fMwSt <> TaxRate*100

-- Mehrwertsteuer der Gutschrift gleich der Amazon Gutschrift setzen
update tgutschrift
set fMwSt = TaxRate*100
FROM tgutschrift
Join pf_amazon_vcs_history
on tgutschrift.cGutschriftNr = pf_amazon_vcs_history.VATInvoiceNumber
where tGutschrift.fMwSt <> TaxRate*100

-- Mehrwertsteuer der Gutschriftspositionen gleich der Amazon Gutschrift setzen
update tGutschriftPos
set tGutschriftPos.fMwSt = TaxRate*100
FROM tGutschriftPos
Join tgutschrift
on tGutschriftPos.tGutschrift_kGutschrift = tgutschrift.kGutschrift
Join pf_amazon_vcs_history
on tgutschrift.cGutschriftNr = pf_amazon_vcs_history.VATInvoiceNumber
where tGutschriftPos.fMwSt <> TaxRate*100

sehr schön, danke!

Bitte VOR den Updates jeweils eine Datensicherung machen☝️

 

[elevennerds.de]

Hallo, bitte die verwendete WAWI-Version ranschreiben, da sich das Tabellen-Layout ändern kann.

 

[JohnFrea]

Interessant.

Aber warum ist

-- Bestellungen mit mehreren MwSt-Sätzen anzeigen
select *
from tbestellpos t1
JOIN (SELECT tBestellung_kBestellung, MAX(fMwSt) as mwstmax, MIN(fMwSt) as mwstmin FROM tbestellpos WHERE fVKPreis <> 0 GROUP BY tBestellung_kBestellung) t2
ON t1.tBestellung_kBestellung = t2.tBestellung_kBestellung
JOIN tBestellung t3
ON t1.tBestellung_kBestellung = t3.kBestellung
and mwstmin <> mwstmax

ein Problem? Aufträge mit Produkten, die unterschiedliche MwSt. Sätze haben sind doch normal...

 

[Manuel Pietzsch]

Hi Freunde,

ich vermisse das die Umrechnung des VK Nettos der Auftragspositionen.
Zur Orientierung: hier bin ich das für die MwSt-Umstellung durch gegangen: https://forum.jtl-software.de/attac...ege_aendern_stand-02-07-2020_15-51-zip.41810/

Um Auftragspositionen sicher zu ändern empfehle ich immer den Weg über die SPs, denn die wissen am Ende wo dazu noch Daten aktualisiert werden müsssen. Deswegen haben wir den Trigger ja eingeführt, damit niemand an den SPs vorbei arbeitet.
z.B. speichern wir zu jedem Auftrag Ecktdaten in tBestellungEckdaten, die bei Änderung von Preisen aktualisiert werden. Dort steht auch der Nettowert der Aufträge der z.B. für Statistiken herangezogen werden kann.

Gruß

Manuel

 

[tierfreund]

Interessant.

Aber warum ist

-- Bestellungen mit mehreren MwSt-Sätzen anzeigen
select *
from tbestellpos t1
JOIN (SELECT tBestellung_kBestellung, MAX(fMwSt) as mwstmax, MIN(fMwSt) as mwstmin FROM tbestellpos WHERE fVKPreis <> 0 GROUP BY tBestellung_kBestellung) t2
ON t1.tBestellung_kBestellung = t2.tBestellung_kBestellung
JOIN tBestellung t3
ON t1.tBestellung_kBestellung = t3.kBestellung
and mwstmin <> mwstmax

ein Problem? Aufträge mit Produkten, die unterschiedliche MwSt. Sätze haben sind doch normal...

Richtig, das ist ja auch nur eine Abfrage und ich behaupte bei >90% der User sollte das eher nicht vorkommen. Insbesondere geht es hier um Amazon-Importe und Amazon VCS kennt keine unterschiedlichen Steuersätze

 

[tierfreund]

Hi Freunde,

ich vermisse das die Umrechnung des VK Nettos der Auftragspositionen.
Zur Orientierung: hier bin ich das für die MwSt-Umstellung durch gegangen: https://forum.jtl-software.de/attac...ege_aendern_stand-02-07-2020_15-51-zip.41810/

Um Auftragspositionen sicher zu ändern empfehle ich immer den Weg über die SPs, denn die wissen am Ende wo dazu noch Daten aktualisiert werden müsssen. Deswegen haben wir den Trigger ja eingeführt, damit niemand an den SPs vorbei arbeitet.
z.B. speichern wir zu jedem Auftrag Ecktdaten in tBestellungEckdaten, die bei Änderung von Preisen aktualisiert werden. Dort steht auch der Nettowert der Aufträge der z.B. für Statistiken herangezogen werden kann.

Gruß

Manuel

Da hast du sicher recht, das ganze ist nur ein Hack. Mein Ziel der Veröffentlichung besteht insbesondere darin aufzuzeigen wo bei mir und eventuell auch anderen Usern Probleme entstehen, damit Ihr das mal fixen könnt. Ich hatte da auch mal ein Ticket aufgemacht, was leider versandet ist. Ich will nicht gehässig sein, aber ich bin auch nur zahlender User und Ihr die Programmierer

Ich lade dich herzlich dazu ein, die Skripte zu verbessern und den Beitrag zu editieren oder eine verbesserte Version zu Posten, damit ich das editieren kann.

 

[tierfreund]

Ich finde inbesondere immer wieder falsche Einträge mit den Skripten:
-- Fehlende IGL bei Amazon-Bestellungen finden
und
-- Fehlerhafte Mehrwertsteuer der Amazon Gutschrift finden

Gibt es hier auch andere Nutzer, welche hier fehlerhafte EInträge finden?