440 lines
17 KiB
SQL
440 lines
17 KiB
SQL
-- =============================================================================
|
||
-- Quartal Schweiz – Gesamt (ODOO + AMAZON), Lieferung in die Schweiz
|
||
-- Odoo 17 / PostgreSQL
|
||
--
|
||
-- Zweck:
|
||
-- Dieselben Spalten wie Alle_Rechnungen.sql (inkl. "Quelle"), aber:
|
||
-- * Zeitraum = QUARTAL statt Monat
|
||
-- * nur Rechnungen mit Lieferung in die SCHWEIZ (Lieferland = 'CH')
|
||
--
|
||
-- Modelle:
|
||
-- ODOO : account.move (Kundenrechnungen / Gutschriften)
|
||
-- AMAZON : brandize_datacenter_amazon_vat_transaction
|
||
--
|
||
-- Parameter:
|
||
-- %(quarter)s Quartal als Text, z. B. '2026-Q3'
|
||
-- ODOO : Rechnungsdatum im Quartal (siehe quarters-CTE)
|
||
-- AMAZON: activity_period in den 3 Monaten des Quartals
|
||
--
|
||
-- Aufruf (Odoo / psycopg2):
|
||
-- cr.execute(open('Quartal_Schweiz.sql').read(), {'quarter': '2026-Q3'})
|
||
--
|
||
-- In psql stattdessen:
|
||
-- \set quarter '2026-Q3'
|
||
--
|
||
-- Diagnose (falls keine AMAZON-Zeilen geliefert werden):
|
||
-- SELECT activity_period, count(*)
|
||
-- FROM brandize_datacenter_amazon_vat_transaction
|
||
-- WHERE arrival_country = 'CH' OR sale_arrival_country = 'CH'
|
||
-- GROUP BY 1 ORDER BY 1;
|
||
-- -- zeigt das tatsaechliche Format von activity_period
|
||
--
|
||
-- ---------------------------------------------------------------------------
|
||
-- Spalten-Mapping ODOO -> AMAZON
|
||
-- (Felder ohne Amazon-Pendant bleiben leer)
|
||
-- ---------------------------------------------------------------------------
|
||
-- Rechnungsdatum <- transaction_complete_date (Fallback: depart)
|
||
-- Rechnungsnummer <- vat_inv_number
|
||
-- Rechnungsland <- Name zu sale_arrival_country (res_country)
|
||
-- Rechnungsland Code <- sale_arrival_country
|
||
-- Zahlart <- '' (kein Amazon-Feld)
|
||
-- Zahlungsreference <- transaction_event_id
|
||
-- USt. Id Kaeufer <- buyer_vat_number
|
||
-- Gesamtpreis brutto <- total_activity_value_amt_vat_incl
|
||
-- Gesamtpreis netto <- total_activity_value_amt_vat_excl
|
||
-- Brutto / -erm <- Steuersplit nach effektivem Satz (Schwelle
|
||
-- USt. Satz / -erm CH >= 8, sonst >= 16 - wie im ODOO-Export)
|
||
-- Netto Betrag / -erm Satz = total_activity_value_vat_amt / netto * 100
|
||
-- Versandkosten brutto <- total_ship_charge_amt_vat_incl
|
||
-- Versandkosten netto <- total_ship_charge_amt_vat_excl
|
||
-- Auftragsdatum <- transaction_depart_date
|
||
-- Bestellnummer <- activity_transaction_id
|
||
-- Zahldatum <- '' (kein Amazon-Feld)
|
||
-- Lieferadresse Name <- buyer_name
|
||
-- Tag <- '' (kein Amazon-Feld)
|
||
-- Lieferland <- Name zu arrival_country (res_country)
|
||
--
|
||
-- ACHTUNG: Das Amazon-Mapping ist eine 1:1-Uebertragung auf die ODOO-Spalten.
|
||
-- Die Datumszuordnung (Rechnungsdatum/Auftragsdatum) sowie Bestellnummer
|
||
-- sollten gegen die tatsaechliche Amazon-Report-Semantik geprueft und hier
|
||
-- ggf. angepasst werden.
|
||
-- =============================================================================
|
||
|
||
WITH params AS (
|
||
-- Quartal aus %(quarter)s (z. B. '2026-Q3') ableiten.
|
||
SELECT
|
||
split_part(%(quarter)s, '-', 1) AS yr,
|
||
right(split_part(%(quarter)s, '-', 2), 1)::int AS qn
|
||
),
|
||
quarters AS (
|
||
SELECT
|
||
yr,
|
||
qn,
|
||
make_date(yr::int, (qn - 1) * 3 + 1, 1) AS first_day,
|
||
make_date(yr::int, (qn - 1) * 3 + 1, 1) + INTERVAL '3 months' AS next_day
|
||
FROM params
|
||
),
|
||
qmonths AS (
|
||
-- Alle moeglichen Schreibweisen der 3 Quartalsmonate als 'YYYY-MMM'
|
||
-- (deutsche UND englische Kuerzel, da die gespeicherte Schreibweise je
|
||
-- nach Herkunft abweichen kann).
|
||
SELECT DISTINCT
|
||
q.yr || '-' || v.abbr AS activity_period
|
||
FROM quarters q
|
||
CROSS JOIN generate_series(1, 3) AS g(n)
|
||
JOIN (VALUES
|
||
(1, 'JAN'), (2, 'FEB'), (3, 'MÄR'), (3, 'MAR'), (4, 'APR'),
|
||
(5, 'MAI'), (5, 'MAY'), (6, 'JUN'), (7, 'JUL'), (8, 'AUG'),
|
||
(9, 'SEP'), (10, 'OKT'), (10, 'OCT'), (11, 'NOV'),
|
||
(12, 'DEZ'), (12, 'DEC')
|
||
) AS v(mnum, abbr) ON v.mnum = (q.qn - 1) * 3 + g.n
|
||
),
|
||
|
||
-- ===========================================================================
|
||
-- ODOO-Seite (identisch zu Rechnungsexport.sql)
|
||
-- ===========================================================================
|
||
|
||
invoices AS (
|
||
SELECT
|
||
am.id,
|
||
am.name,
|
||
am.ref,
|
||
am.move_type,
|
||
am.invoice_date,
|
||
am.invoice_origin,
|
||
am.partner_id,
|
||
am.partner_shipping_id,
|
||
am.invoice_payment_term_id,
|
||
am.payment_reference
|
||
FROM account_move am
|
||
CROSS JOIN quarters q
|
||
WHERE am.invoice_date >= q.first_day
|
||
AND am.invoice_date < q.next_day
|
||
AND am.move_type IN ('out_invoice', 'out_refund')
|
||
),
|
||
|
||
line_taxes AS (
|
||
SELECT
|
||
aml.move_id AS invoice_id,
|
||
at.amount AS tax_rate,
|
||
SUM(aml.price_subtotal) AS netto,
|
||
SUM(aml.price_total) AS brutto
|
||
FROM invoices inv
|
||
JOIN account_move_line aml
|
||
ON aml.move_id = inv.id
|
||
JOIN account_move_line_account_tax_rel amlatr
|
||
ON amlatr.account_move_line_id = aml.id
|
||
JOIN account_tax at
|
||
ON at.id = amlatr.account_tax_id
|
||
GROUP BY aml.move_id, at.amount
|
||
),
|
||
|
||
tax_split AS (
|
||
SELECT
|
||
lt.invoice_id,
|
||
|
||
MAX(CASE
|
||
WHEN (rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
|
||
OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
|
||
THEN ROUND(lt.brutto::numeric, 2)
|
||
END) AS brutto_normal,
|
||
|
||
MAX(CASE
|
||
WHEN (rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
|
||
OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
|
||
THEN ROUND(lt.tax_rate::numeric, 2)
|
||
END) AS ust_satz_normal,
|
||
|
||
MAX(CASE
|
||
WHEN (rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
|
||
OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
|
||
THEN ROUND(lt.netto::numeric, 2)
|
||
END) AS netto_normal,
|
||
|
||
MAX(CASE
|
||
WHEN NOT (
|
||
(rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
|
||
OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
|
||
)
|
||
THEN ROUND(lt.brutto::numeric, 2)
|
||
END) AS brutto_erm,
|
||
|
||
MAX(CASE
|
||
WHEN NOT (
|
||
(rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
|
||
OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
|
||
)
|
||
THEN ROUND(lt.tax_rate::numeric, 2)
|
||
END) AS ust_satz_erm,
|
||
|
||
MAX(CASE
|
||
WHEN NOT (
|
||
(rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
|
||
OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
|
||
)
|
||
THEN ROUND(lt.netto::numeric, 2)
|
||
END) AS netto_erm
|
||
|
||
FROM line_taxes lt
|
||
JOIN invoices inv
|
||
ON inv.id = lt.invoice_id
|
||
JOIN res_partner rp
|
||
ON rp.id = inv.partner_id
|
||
LEFT JOIN res_country rc
|
||
ON rc.id = rp.country_id
|
||
GROUP BY lt.invoice_id
|
||
),
|
||
|
||
shipping AS (
|
||
SELECT
|
||
aml.move_id AS invoice_id,
|
||
ROUND(SUM(aml.price_total)::numeric, 2) AS shipping_brutto,
|
||
ROUND(SUM(aml.price_subtotal)::numeric, 2) AS shipping_netto
|
||
FROM invoices inv
|
||
JOIN account_move_line aml
|
||
ON aml.move_id = inv.id
|
||
JOIN product_product pp
|
||
ON pp.id = aml.product_id
|
||
JOIN product_template pt
|
||
ON pt.id = pp.product_tmpl_id
|
||
WHERE pt.categ_id = 9
|
||
GROUP BY aml.move_id
|
||
),
|
||
|
||
invoice_tags AS (
|
||
SELECT
|
||
inv.id AS invoice_id,
|
||
STRING_AGG(
|
||
DISTINCT COALESCE(ct.name->>'en_US', ct.name->>'de_DE', ''),
|
||
', ' ORDER BY COALESCE(ct.name->>'en_US', ct.name->>'de_DE', '')
|
||
) AS tags
|
||
FROM invoices inv
|
||
JOIN sale_order so
|
||
ON so.name = inv.invoice_origin
|
||
JOIN sale_order_tag_rel sotr
|
||
ON sotr.order_id = so.id
|
||
JOIN crm_tag ct
|
||
ON ct.id = sotr.tag_id
|
||
GROUP BY inv.id
|
||
),
|
||
|
||
invoice_transaction_reference AS (
|
||
SELECT
|
||
rel.invoice_id,
|
||
pt.provider_reference,
|
||
ROW_NUMBER() OVER (
|
||
PARTITION BY rel.invoice_id
|
||
ORDER BY pt.id
|
||
) AS rn
|
||
FROM account_invoice_transaction_rel rel
|
||
JOIN payment_transaction pt
|
||
ON pt.id = rel.transaction_id
|
||
),
|
||
|
||
sale_info AS (
|
||
SELECT
|
||
inv.id AS invoice_id,
|
||
MIN(so.date_order) AS order_date
|
||
FROM invoices inv
|
||
JOIN sale_order so
|
||
ON so.name = inv.invoice_origin
|
||
GROUP BY inv.id
|
||
),
|
||
|
||
invoice_payments AS (
|
||
SELECT
|
||
aml.move_id AS invoice_id,
|
||
MAX(apr.max_date) AS payment_date
|
||
FROM invoices inv
|
||
JOIN account_move_line aml
|
||
ON aml.move_id = inv.id
|
||
JOIN account_account aa
|
||
ON aa.id = aml.account_id
|
||
AND aa.account_type = 'asset_receivable'
|
||
JOIN account_partial_reconcile apr
|
||
ON apr.debit_move_id = aml.id
|
||
OR apr.credit_move_id = aml.id
|
||
GROUP BY aml.move_id
|
||
),
|
||
|
||
odoo_rows AS (
|
||
SELECT
|
||
TO_CHAR(inv.invoice_date, 'YYYY-MM-DD') AS "Rechnungsdatum",
|
||
|
||
CASE
|
||
WHEN inv.move_type = 'out_refund'
|
||
THEN COALESCE(inv.name, inv.ref, '') || '-gs'
|
||
ELSE COALESCE(inv.name, inv.ref, '')
|
||
END AS "Rechnungsnummer",
|
||
|
||
COALESCE(rc_inv.name->>'en_US', rc_inv.name->>'de_DE', '') AS "Rechnungsland",
|
||
COALESCE(rc_inv.code, '') AS "Rechnungsland Ländercode",
|
||
COALESCE(apt.name->>'en_US', apt.name->>'de_DE', '') AS "Zahlart",
|
||
|
||
COALESCE(itr.provider_reference, '') AS "Zahlungsreference der Bestellung",
|
||
COALESCE(partner.vat, '') AS "USt. Id / VAT Id des Käufers (in Bestellung)",
|
||
|
||
ROUND(COALESCE(ts.brutto_normal, 0) + COALESCE(ts.brutto_erm, 0), 2) AS "Gesamtpreis brutto",
|
||
ROUND(COALESCE(ts.netto_normal, 0) + COALESCE(ts.netto_erm, 0), 2) AS "Gesamtpreis netto",
|
||
|
||
COALESCE(ts.brutto_normal, 0) AS "Brutto",
|
||
COALESCE(ts.brutto_erm, 0) AS "Brutto erm",
|
||
COALESCE(ts.ust_satz_normal, 0) AS "USt. Satz",
|
||
COALESCE(ts.ust_satz_erm, 0) AS "Ust. Satz erm",
|
||
COALESCE(ts.netto_normal, 0) AS "Netto Betrag",
|
||
COALESCE(ts.netto_erm, 0) AS "Netto Betrag erm",
|
||
|
||
COALESCE(sh.shipping_brutto, 0) AS "Versandkosten brutto",
|
||
COALESCE(sh.shipping_netto, 0) AS "Versandkosten netto",
|
||
|
||
COALESCE(TO_CHAR(si.order_date, 'YYYY-MM-DD'), '') AS "Auftragsdatum",
|
||
COALESCE(inv.invoice_origin, '') AS "Bestellnummer",
|
||
COALESCE(TO_CHAR(ip.payment_date, 'YYYY-MM-DD'), '') AS "Zahldatum",
|
||
|
||
COALESCE(ship.name, partner.name, '') AS "Lieferadresse Name",
|
||
COALESCE(it.tags, '') AS "Tag",
|
||
COALESCE(
|
||
rc_ship.name->>'en_US', rc_ship.name->>'de_DE',
|
||
rc_inv.name->>'en_US', rc_inv.name->>'de_DE',
|
||
''
|
||
) AS "Lieferland"
|
||
|
||
FROM invoices inv
|
||
JOIN res_partner partner
|
||
ON partner.id = inv.partner_id
|
||
LEFT JOIN res_country rc_inv
|
||
ON rc_inv.id = partner.country_id
|
||
LEFT JOIN res_partner ship
|
||
ON ship.id = inv.partner_shipping_id
|
||
LEFT JOIN res_country rc_ship
|
||
ON rc_ship.id = ship.country_id
|
||
LEFT JOIN account_payment_term apt
|
||
ON apt.id = inv.invoice_payment_term_id
|
||
LEFT JOIN tax_split ts
|
||
ON ts.invoice_id = inv.id
|
||
LEFT JOIN shipping sh
|
||
ON sh.invoice_id = inv.id
|
||
LEFT JOIN invoice_tags it
|
||
ON it.invoice_id = inv.id
|
||
LEFT JOIN invoice_transaction_reference itr
|
||
ON itr.invoice_id = inv.id
|
||
AND itr.rn = 1
|
||
LEFT JOIN sale_info si
|
||
ON si.invoice_id = inv.id
|
||
LEFT JOIN invoice_payments ip
|
||
ON ip.invoice_id = inv.id
|
||
-- Nur Lieferungen in die Schweiz (Versandland, Fallback: Rechnungsland).
|
||
WHERE COALESCE(rc_ship.code, rc_inv.code) = 'CH'
|
||
),
|
||
|
||
-- ===========================================================================
|
||
-- AMAZON-Seite
|
||
-- ===========================================================================
|
||
|
||
-- Basiszahlen + effektiver USt-Satz je Transaktionszeile.
|
||
amazon_base AS (
|
||
SELECT
|
||
a.transaction_complete_date,
|
||
a.transaction_depart_date,
|
||
a.vat_inv_number,
|
||
a.sale_arrival_country,
|
||
a.arrival_country,
|
||
a.transaction_event_id,
|
||
a.buyer_vat_number,
|
||
a.buyer_name,
|
||
a.activity_transaction_id,
|
||
COALESCE(a.total_activity_value_amt_vat_incl, 0) AS gross,
|
||
COALESCE(a.total_activity_value_amt_vat_excl, 0) AS net,
|
||
COALESCE(a.total_ship_charge_amt_vat_incl, 0) AS ship_gross,
|
||
COALESCE(a.total_ship_charge_amt_vat_excl, 0) AS ship_net,
|
||
CASE
|
||
WHEN COALESCE(a.total_activity_value_amt_vat_excl, 0) <> 0
|
||
THEN ROUND((COALESCE(a.total_activity_value_vat_amt, 0)
|
||
/ a.total_activity_value_amt_vat_excl * 100)::numeric, 2)
|
||
ELSE 0
|
||
END AS rate
|
||
FROM brandize_datacenter_amazon_vat_transaction a
|
||
WHERE upper(a.activity_period) IN (SELECT upper(activity_period) FROM qmonths)
|
||
-- Lieferung in die Schweiz: Ziel-/Versandland kann in arrival_country
|
||
-- oder sale_arrival_country stehen.
|
||
AND (upper(a.arrival_country) = 'CH' OR upper(a.sale_arrival_country) = 'CH')
|
||
-- Optional: Gebuehrenzeilen ausschliessen (wie in VAT_Summary.sql):
|
||
-- AND a.sales_channel <> 'AMAZON_FEE'
|
||
),
|
||
|
||
amazon_rows AS (
|
||
SELECT
|
||
TO_CHAR(COALESCE(b.transaction_complete_date, b.transaction_depart_date), 'YYYY-MM-DD')
|
||
AS "Rechnungsdatum",
|
||
COALESCE(b.vat_inv_number, '') AS "Rechnungsnummer",
|
||
COALESCE(rc_inv.name->>'en_US', rc_inv.name->>'de_DE', '') AS "Rechnungsland",
|
||
COALESCE(b.sale_arrival_country, '') AS "Rechnungsland Ländercode",
|
||
'' AS "Zahlart",
|
||
COALESCE(b.transaction_event_id, '') AS "Zahlungsreference der Bestellung",
|
||
COALESCE(b.buyer_vat_number, '') AS "USt. Id / VAT Id des Käufers (in Bestellung)",
|
||
|
||
ROUND(b.gross::numeric, 2) AS "Gesamtpreis brutto",
|
||
ROUND(b.net::numeric, 2) AS "Gesamtpreis netto",
|
||
|
||
CASE
|
||
WHEN (b.sale_arrival_country = 'CH' AND b.rate >= 8)
|
||
OR (COALESCE(b.sale_arrival_country, '') <> 'CH' AND b.rate >= 16)
|
||
THEN ROUND(b.gross::numeric, 2) ELSE 0
|
||
END AS "Brutto",
|
||
CASE
|
||
WHEN NOT (
|
||
(b.sale_arrival_country = 'CH' AND b.rate >= 8)
|
||
OR (COALESCE(b.sale_arrival_country, '') <> 'CH' AND b.rate >= 16)
|
||
)
|
||
THEN ROUND(b.gross::numeric, 2) ELSE 0
|
||
END AS "Brutto erm",
|
||
CASE
|
||
WHEN (b.sale_arrival_country = 'CH' AND b.rate >= 8)
|
||
OR (COALESCE(b.sale_arrival_country, '') <> 'CH' AND b.rate >= 16)
|
||
THEN b.rate ELSE 0
|
||
END AS "USt. Satz",
|
||
CASE
|
||
WHEN NOT (
|
||
(b.sale_arrival_country = 'CH' AND b.rate >= 8)
|
||
OR (COALESCE(b.sale_arrival_country, '') <> 'CH' AND b.rate >= 16)
|
||
)
|
||
THEN b.rate ELSE 0
|
||
END AS "Ust. Satz erm",
|
||
CASE
|
||
WHEN (b.sale_arrival_country = 'CH' AND b.rate >= 8)
|
||
OR (COALESCE(b.sale_arrival_country, '') <> 'CH' AND b.rate >= 16)
|
||
THEN ROUND(b.net::numeric, 2) ELSE 0
|
||
END AS "Netto Betrag",
|
||
CASE
|
||
WHEN NOT (
|
||
(b.sale_arrival_country = 'CH' AND b.rate >= 8)
|
||
OR (COALESCE(b.sale_arrival_country, '') <> 'CH' AND b.rate >= 16)
|
||
)
|
||
THEN ROUND(b.net::numeric, 2) ELSE 0
|
||
END AS "Netto Betrag erm",
|
||
|
||
ROUND(b.ship_gross::numeric, 2) AS "Versandkosten brutto",
|
||
ROUND(b.ship_net::numeric, 2) AS "Versandkosten netto",
|
||
|
||
COALESCE(TO_CHAR(b.transaction_depart_date, 'YYYY-MM-DD'), '') AS "Auftragsdatum",
|
||
COALESCE(b.activity_transaction_id, '') AS "Bestellnummer",
|
||
'' AS "Zahldatum",
|
||
COALESCE(b.buyer_name, '') AS "Lieferadresse Name",
|
||
'' AS "Tag",
|
||
COALESCE(rc_ship.name->>'en_US', rc_ship.name->>'de_DE', '') AS "Lieferland"
|
||
|
||
FROM amazon_base b
|
||
LEFT JOIN res_country rc_inv
|
||
ON rc_inv.code = b.sale_arrival_country
|
||
LEFT JOIN res_country rc_ship
|
||
ON rc_ship.code = b.arrival_country
|
||
)
|
||
|
||
-- ===========================================================================
|
||
-- Zusammenfuehrung: ODOO + AMAZON, je Zeile mit Quelle
|
||
-- ===========================================================================
|
||
SELECT *, 'ODOO' AS "Quelle" FROM odoo_rows
|
||
UNION ALL
|
||
SELECT *, 'AMAZON' AS "Quelle" FROM amazon_rows
|
||
ORDER BY "Rechnungsdatum", "Rechnungsnummer";
|