106 lines
4.5 KiB
SQL
106 lines
4.5 KiB
SQL
-- ============================================================================
|
||
-- VAT Summary (Kontrolle Umsatzsteuer-Voranmeldung)
|
||
-- Odoo 17 / PostgreSQL
|
||
--
|
||
-- Modell : brandize.datacenter.amazon.vat.transaction
|
||
-- Tabelle: brandize_datacenter_amazon_vat_transaction
|
||
--
|
||
-- Rechnungsdatum von / bis : 01.09.2026 / 30.09.2026
|
||
--
|
||
-- Parameter:
|
||
-- %(month)s Abrechnungszeitraum als Text, z. B. '2026-SEP'
|
||
--
|
||
-- Aufruf (Odoo / psycopg2):
|
||
-- cr.execute(open('VAT_Summary.sql').read(), {'month': '2026-SEP'})
|
||
--
|
||
-- In psql:
|
||
-- \set month '2026-SEP'
|
||
-- ... WHERE activity_period = :'month'
|
||
--
|
||
-- Excel-Namen -> SQL-Spalten:
|
||
-- QUANTITY -> qty
|
||
-- GROSS_INVOICE_AMOUNT -> total_activity_value_amt_vat_incl
|
||
-- TOTAL_VAT -> total_activity_value_vat_amt
|
||
-- IN_PERIOD -> activity_period = %(month)s
|
||
-- SALES_CHANNEL -> sales_channel (<> 'AMAZON_FEE')
|
||
-- SALE_ARRIVAL_COUNTRY -> sale_arrival_country
|
||
-- SALE_DEPART_COUNTRY -> sale_depart_country
|
||
-- BUYER_VAT_NR_COUNTRY -> buyer_vat_number_country
|
||
-- ZERO_HAS_B2B_VAT_NR -> buyer_vat_number <> ''
|
||
--
|
||
-- Hinweis: Die "bereinigten" Hilfsspalten (CU-CX) sind nur ein Excel-Workaround
|
||
-- fuer als Text/Datum importierte Zellen. In der DB sind die numerischen
|
||
-- Spalten bereits korrekt typisiert, daher werden hier die Rohspalten genutzt.
|
||
-- ============================================================================
|
||
|
||
WITH base AS (
|
||
SELECT
|
||
sale_arrival_country,
|
||
sale_depart_country,
|
||
buyer_vat_number_country,
|
||
buyer_vat_number,
|
||
qty,
|
||
total_activity_value_amt_vat_incl AS gross,
|
||
total_activity_value_vat_amt AS vat
|
||
FROM brandize_datacenter_amazon_vat_transaction
|
||
WHERE activity_period = %(month)s
|
||
AND sales_channel <> 'AMAZON_FEE'
|
||
),
|
||
arrival AS (
|
||
SELECT
|
||
sale_arrival_country AS country_code,
|
||
SUM(qty) AS quantity,
|
||
SUM(gross) AS gross_total,
|
||
-- Non-OSS: Abgangs- und Ankunftsland identisch (Inlandslieferung)
|
||
SUM(gross) FILTER (WHERE sale_depart_country = sale_arrival_country) AS gross_non_oss,
|
||
SUM(vat) FILTER (WHERE sale_depart_country = sale_arrival_country) AS vat_non_oss,
|
||
-- Excl. B2B: nur Umsaetze mit USt (TOTAL_VAT <> 0)
|
||
SUM(gross) FILTER (WHERE vat <> 0) AS gross_excl_b2b
|
||
FROM base
|
||
WHERE sale_arrival_country IS NOT NULL
|
||
AND sale_arrival_country <> ''
|
||
GROUP BY sale_arrival_country
|
||
),
|
||
intra AS (
|
||
-- Innergemeinschaftliche Lieferung: B2B mit USt-Nr. und 0 %% USt
|
||
SELECT
|
||
buyer_vat_number_country AS country_code,
|
||
SUM(gross) AS intra_net
|
||
FROM base
|
||
WHERE buyer_vat_number_country IS NOT NULL
|
||
AND buyer_vat_number_country <> ''
|
||
AND buyer_vat_number IS NOT NULL
|
||
AND buyer_vat_number <> ''
|
||
AND vat = 0
|
||
GROUP BY buyer_vat_number_country
|
||
)
|
||
SELECT
|
||
a.country_code AS "Country Code",
|
||
COALESCE(c.name->>'de_DE', c.name->>'en_US', a.country_code) AS "Land",
|
||
to_char(a.quantity, 'FM999,999,999') AS "Quantity",
|
||
translate(to_char(a.gross_total, 'FM999,999,999,999.99'), ',.', '.,') || ' €' AS "Gross Sales (Total)",
|
||
translate(to_char(a.gross_non_oss, 'FM999,999,999,999.99'), ',.', '.,') || ' €' AS "Gross Sales (Non-OSS)",
|
||
translate(to_char(a.vat_non_oss, 'FM999,999,999,999.99'), ',.', '.,') || ' €' AS "VAT to Remit (Non-OSS)",
|
||
translate(to_char(a.gross_excl_b2b, 'FM999,999,999,999.99'), ',.', '.,') || ' €' AS "Gross Sales (Excl. B2B)",
|
||
translate(to_char(COALESCE(i.intra_net, 0), 'FM999,999,999,999.99'), ',.', '.,') || ' €'
|
||
AS "Intra-community supply – Net sales"
|
||
FROM arrival a
|
||
LEFT JOIN intra i ON i.country_code = a.country_code
|
||
LEFT JOIN res_country c ON c.code = a.country_code
|
||
ORDER BY a.country_code;
|
||
|
||
-- ----------------------------------------------------------------------------
|
||
-- Import-Kontrolle (entspricht der LET-Pruefung im Sheet):
|
||
-- zaehlt Datensaetze, deren Betragsspalten nicht als Zahl vorliegen.
|
||
--
|
||
-- SELECT count(*) AS nicht_numerische_zellen
|
||
-- FROM brandize_datacenter_amazon_vat_transaction
|
||
-- WHERE activity_period = %(month)s
|
||
-- AND (total_activity_value_amt_vat_incl IS NULL
|
||
-- OR total_activity_value_vat_amt IS NULL);
|
||
--
|
||
-- Hinweis: Die Zeilenreihenfolge im Sheet ist die Erstauftretens-Reihenfolge
|
||
-- (UNIQUE(FILTER(...))). Falls gewuenscht, statt "ORDER BY a.country_code"
|
||
-- ein "MIN(id) AS first_seen" in der arrival-CTE ergaenzen und danach sortieren.
|
||
-- ----------------------------------------------------------------------------
|