← Files VeraARCHIVED FILE
modules/open-item-reconciliation/scripts/build_missing_evidence_requests.py
62 KB · Oct 4, 2026 · 12:28 UTC
"""Build targeted evidence requests from a reconciliation workbook."""
from __future__ import annotations
import sys as _bootstrap_sys
_bootstrap_sys.dont_write_bytecode = True
_bootstrap_sys.pycache_prefix = (
r"Z:\__open_item_reconciliation_no_bytecode__"
if _bootstrap_sys.platform == "win32"
else "/dev/null/open-item-reconciliation"
)
import os as _bootstrap_os
_BOOTSTRAP_PATH = _bootstrap_os.path.join(
_bootstrap_os.path.dirname(_bootstrap_os.path.abspath(__file__)),
"implementation_bootstrap.py",
)
_BOOTSTRAP_NAMESPACE = {
"__file__": _BOOTSTRAP_PATH,
"__name__": "_open_item_reconciliation_implementation_bootstrap",
}
_bootstrap_stat = _bootstrap_os.lstat(_BOOTSTRAP_PATH)
if _bootstrap_stat.st_mode & 0o170000 != 0o100000 or _bootstrap_stat.st_nlink != 1:
raise RuntimeError(
"implementation bootstrap must be an ordinary single-link regular file"
)
_bootstrap_descriptor = _bootstrap_os.open(
_BOOTSTRAP_PATH,
_bootstrap_os.O_RDONLY | getattr(_bootstrap_os, "O_NOFOLLOW", 0),
)
try:
_bootstrap_open_stat = _bootstrap_os.fstat(_bootstrap_descriptor)
_bootstrap_identity = (
_bootstrap_stat.st_dev,
_bootstrap_stat.st_ino,
_bootstrap_stat.st_size,
_bootstrap_stat.st_mtime_ns,
_bootstrap_stat.st_nlink,
)
if _bootstrap_identity != (
_bootstrap_open_stat.st_dev,
_bootstrap_open_stat.st_ino,
_bootstrap_open_stat.st_size,
_bootstrap_open_stat.st_mtime_ns,
_bootstrap_open_stat.st_nlink,
):
raise RuntimeError("implementation bootstrap changed before it was read")
with _bootstrap_os.fdopen(
_bootstrap_descriptor,
"rb",
closefd=False,
) as _bootstrap_handle:
_bootstrap_source = _bootstrap_handle.read()
_bootstrap_after_stat = _bootstrap_os.fstat(_bootstrap_descriptor)
if (
_bootstrap_identity
!= (
_bootstrap_after_stat.st_dev,
_bootstrap_after_stat.st_ino,
_bootstrap_after_stat.st_size,
_bootstrap_after_stat.st_mtime_ns,
_bootstrap_after_stat.st_nlink,
)
or len(_bootstrap_source) != _bootstrap_after_stat.st_size
):
raise RuntimeError("implementation bootstrap changed while it was read")
finally:
_bootstrap_os.close(_bootstrap_descriptor)
# Execute only the pre-opened single-link bootstrap source.
exec( # nosec B102
compile(_bootstrap_source, _BOOTSTRAP_PATH, "exec"),
_BOOTSTRAP_NAMESPACE,
)
_BOOTSTRAP_ROOTS = _BOOTSTRAP_NAMESPACE["activate_implementation_boundary"](
("locale_support",)
)
_SCRIPTS_DIR = _bootstrap_os.path.dirname(_bootstrap_os.path.abspath(__file__))
if _SCRIPTS_DIR not in _bootstrap_sys.path:
_bootstrap_sys.path.insert(0, _SCRIPTS_DIR)
import argparse
import logging
import sys
from dataclasses import dataclass
from decimal import Decimal, InvalidOperation
from pathlib import Path
from typing import Any
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter
_VERA_ASSURANCE_MODULE_ROOT = Path(str(_BOOTSTRAP_ROOTS["shared_assurance"])).parent
if str(_VERA_ASSURANCE_MODULE_ROOT) not in sys.path:
sys.path.insert(0, str(_VERA_ASSURANCE_MODULE_ROOT))
from vera_assurance import ( # noqa: E402
AssuranceContractError,
load_client_engagement_context_file,
)
try:
from .locale_support import normalize_language
except ImportError: # pragma: no cover - direct import support
scripts_dir = Path(__file__).resolve().parent
if str(scripts_dir) not in sys.path:
sys.path.insert(0, str(scripts_dir))
from locale_support import normalize_language # type: ignore
__all__ = [
"MissingEvidenceRequestPack",
"build_missing_evidence_request_pack",
"load_reconciliation_context",
"write_missing_evidence_workbook",
]
LOGGER = logging.getLogger(__name__)
DEFAULT_EXCEL_NAME = "richieste_mirate_evidenze.xlsx"
HEADER_FILL = "17365D"
SECTION_ORDER = (
"reconciled_strong",
"probable_payment",
"accounting_support_needed",
"missing_evidence_needed",
"open_balance_confirmation",
"unresolved",
)
REQUEST_FIELDS = (
"row_id",
"side",
"document",
"document_date",
"amount",
"operational_category",
"operational_criterion",
"operational_owner",
"available_evidence",
"targeted_missing_item",
"existing_reference",
"requested_action",
"evidence_description",
)
INSTRUCTION_FIELDS = ("principle", "guidance")
SUMMARY_FIELDS = ("section", "rows", "amount_total")
INVENTORY_FIELDS = (
"evidence_category",
"normalized_rows",
"source_files",
"operational_use",
)
TEXT: dict[str, dict[str, Any]] = {
"it": {
"sheet_names": {
"instructions": "istruzioni",
"summary": "sintesi",
"inventory": "evidenze_disponibili",
"reconciled_strong": "riconciliate_forti",
"probable_payment": "pagamenti_probabili",
"accounting_support_needed": "scritture_da_supportare",
"missing_evidence_needed": "evidenze_da_integrare",
"open_balance_confirmation": "saldi_aperti",
"unresolved": "non_risolte",
},
"headers": {
"principle": "principio",
"guidance": "indicazione",
"section": "sezione",
"rows": "righe",
"amount_total": "importo_totale",
"evidence_category": "categoria_evidenza",
"normalized_rows": "righe_normalizzate",
"source_files": "file_sorgente",
"operational_use": "uso_operativo",
"row_id": "id_riga_workpaper",
"side": "lato",
"document": "documento",
"document_date": "data_documento",
"amount": "importo",
"operational_category": "categoria_operativa",
"operational_criterion": "criterio_operativo",
"operational_owner": "destinatario_operativo",
"available_evidence": "evidenza_gia_disponibile",
"targeted_missing_item": "dato_mancante_mirato",
"existing_reference": "riferimento_esistente",
"requested_action": "azione_richiesta",
"evidence_description": "descrizione_evidenza",
},
"sections": {
"reconciled_strong": "Riconciliate con evidenza forte",
"probable_payment": "Pagamenti probabili da allocare",
"accounting_support_needed": "Scritture contabili da supportare",
"missing_evidence_needed": "Evidenze da integrare",
"open_balance_confirmation": "Saldi aperti da confermare",
"unresolved": "Non risolte",
},
"criteria": {
"reconciled_strong": "Evidenza esterna o catena contabile-bancaria sufficiente.",
"probable_payment": "Movimento bancario o distinta probabile, allocazione non ancora confermata.",
"accounting_support_needed": "Scrittura contabile di chiusura priva di supporto esterno sufficiente.",
"missing_evidence_needed": "Indizio presente ma prova conclusiva mancante.",
"open_balance_confirmation": "Saldo aperto supportato dalle evidenze interne.",
"unresolved": "Collegamento insufficiente nelle evidenze acquisite.",
},
"side": {
"customer": "cliente",
"receivable": "cliente",
"client": "cliente",
"supplier": "fornitore",
"payable": "fornitore",
"vendor": "fornitore",
"unknown": "non indicato",
},
"owners": {
"default_entity": "soggetto revisionato",
"default_counterparty": "controparte",
"counterparty_confirmation": "{entity}; eventuale conferma saldo {counterparty}",
},
"inventory_usage": {
"open_items": "Popolazione delle partite aperte o da riconciliare.",
"ledger": "Supporto di saldo, partitario, mastro e registrazioni interne.",
"journal": "Scritture contabili, chiusure interne, giroconti e movimenti di periodo.",
"bank_statement": "Incassi e pagamenti effettivi da estratto conto.",
"payment_order": "Distinte o batch utili per collegare banca e fatture.",
"factoring_statement": "Evidenza factor, anticipo o operatore esterno.",
"compensation_support": "Supporto di compensazione o netting documentato.",
"unknown": "Fonte acquisita ma non classificata automaticamente.",
"default": "Evidenza acquisita nel fascicolo.",
},
"available": {
"reconciled_strong": "La riga e gia riconciliata nel workpaper con evidenza forte.",
"probable_payment": "Estratto banca e candidato di match gia presenti nel workpaper.",
"accounting_support_needed": "Partita aperta e scrittura contabile gia rilevate.",
"missing_evidence_needed": "Indizio o documento ponte gia rilevato, ma non conclusivo.",
"open_balance_confirmation": "Partita aperta e supporto interno di saldo gia presenti.",
"unresolved": "Partita aperta presente, senza collegamento sufficiente nelle evidenze acquisite.",
"reference": " Riferimento: {reference}.",
},
"missing": {
"reconciled_strong": "Nessun dato mancante per la richiesta mirata.",
"probable_payment": "Conferma allocazione fattura-per-fattura del movimento bancario o della distinta gia identificata.",
"accounting_support_needed": "Supporto esterno o spiegazione documentata della scrittura contabile: banca, factor, compensazione, giroconto o chiusura non-cash.",
"missing_evidence_needed": "Documento necessario per trasformare l'indizio in evidenza conclusiva.",
"open_balance_confirmation": "Conferma che la partita era effettivamente aperta al {cutoff}, oppure prova puntuale della chiusura non rilevata.",
"unresolved": "Riferimento sorgente o prova puntuale di chiusura non rilevata.",
},
"actions": {
"reconciled_strong": "Nessuna richiesta operativa; conservare il riferimento nel workpaper.",
"probable_payment": "Confermare o correggere la mappatura proposta, indicando per ogni fattura importo allocato, distinta/SEPA e data pagamento.",
"accounting_support_needed": "Inviare solo il supporto della contropartita o spiegare la natura della chiusura non-cash; non rimandare mastro/giornale se invariati.",
"missing_evidence_needed": "Indicare quale documento gia acquisito chiude la riga, oppure fornire il solo documento mancante indicato.",
"open_balance_confirmation": "Se aperta: confermare il saldo. Se chiusa: indicare documento di pagamento/incasso/factor/compensazione e data.",
"unresolved": "Fornire riferimento fattura/partitario o indicare se la riga e errata; se chiusa, allegare il documento di chiusura.",
},
"instructions": {
"no_reask": "Non richiedere documenti gia acquisiti",
"no_reask_body": "Il workpaper principale contiene gia la popolazione delle partite aperte, mastri, giornale, estratti banca/factor, distinte e normalizzazioni disponibili.",
"targeted": "Richiesta mirata",
"targeted_body": "Per ogni riga chiedere solo il tassello mancante indicato: allocazione, supporto esterno, conferma aperto o riferimento sorgente.",
"owner": "Destinatario",
"owner_body": "{entity} deve prima confermare/correggere usando il fascicolo. {counterparty} serve solo per eventuale conferma saldo o riconciliazione esterna al {cutoff}.",
},
"log_path": "Workbook richieste mirate: {path}",
},
"fr": {
"sheet_names": {
"instructions": "instructions",
"summary": "synthese",
"inventory": "preuves_disponibles",
"reconciled_strong": "rapprochees_preuve_forte",
"probable_payment": "paiements_probables",
"accounting_support_needed": "ecritures_a_justifier",
"missing_evidence_needed": "preuves_a_completer",
"open_balance_confirmation": "soldes_ouverts",
"unresolved": "non_resolues",
},
"headers": {
"principle": "principe",
"guidance": "indication",
"section": "section",
"rows": "lignes",
"amount_total": "montant_total",
"evidence_category": "categorie_preuve",
"normalized_rows": "lignes_normalisees",
"source_files": "fichier_source",
"operational_use": "usage_operationnel",
"row_id": "id_ligne_workpaper",
"side": "sens",
"document": "document",
"document_date": "date_document",
"amount": "montant",
"operational_category": "categorie_operationnelle",
"operational_criterion": "critere_operationnel",
"operational_owner": "destinataire_operationnel",
"available_evidence": "preuve_deja_disponible",
"targeted_missing_item": "element_manquant_cible",
"existing_reference": "reference_existante",
"requested_action": "action_demandee",
"evidence_description": "description_preuve",
},
"sections": {
"reconciled_strong": "Rapprochees avec preuve forte",
"probable_payment": "Paiements probables a affecter",
"accounting_support_needed": "Ecritures comptables a justifier",
"missing_evidence_needed": "Preuves a completer",
"open_balance_confirmation": "Soldes ouverts a confirmer",
"unresolved": "Non resolues",
},
"criteria": {
"reconciled_strong": "Preuve externe ou chaine comptable-bancaire suffisante.",
"probable_payment": "Mouvement bancaire ou remise probable, affectation non encore confirmee.",
"accounting_support_needed": "Ecriture comptable de cloture sans support externe suffisant.",
"missing_evidence_needed": "Indice present mais preuve concluante manquante.",
"open_balance_confirmation": "Solde ouvert supporte par les preuves internes.",
"unresolved": "Lien insuffisant dans les preuves disponibles.",
},
"side": {
"customer": "client",
"receivable": "client",
"client": "client",
"supplier": "fournisseur",
"payable": "fournisseur",
"vendor": "fournisseur",
"unknown": "non indique",
},
"owners": {
"default_entity": "entite auditee",
"default_counterparty": "contrepartie",
"counterparty_confirmation": "{entity}; confirmation eventuelle du solde {counterparty}",
},
"inventory_usage": {
"open_items": "Population des postes ouverts ou a rapprocher.",
"ledger": "Support de solde, grand livre, auxiliaire et ecritures internes.",
"journal": "Ecritures comptables, clotures internes, virements internes et mouvements de periode.",
"bank_statement": "Encaissements et paiements effectifs selon releve bancaire.",
"payment_order": "Remises ou lots utiles pour relier banque et factures.",
"factoring_statement": "Preuve factor, avance ou operateur externe.",
"compensation_support": "Support de compensation ou netting documente.",
"unknown": "Source recue mais non classee automatiquement.",
"default": "Preuve disponible dans le dossier.",
},
"available": {
"reconciled_strong": "La ligne est deja rapprochee dans le workpaper avec une preuve forte.",
"probable_payment": "Releve bancaire et candidat de rapprochement deja presents dans le workpaper.",
"accounting_support_needed": "Poste ouvert et ecriture comptable deja identifies.",
"missing_evidence_needed": "Indice ou document de liaison deja identifie, mais non concluant.",
"open_balance_confirmation": "Poste ouvert et support interne de solde deja presents.",
"unresolved": "Poste ouvert present, sans lien suffisant dans les preuves disponibles.",
"reference": " Reference: {reference}.",
},
"missing": {
"reconciled_strong": "Aucun element manquant pour la demande ciblee.",
"probable_payment": "Confirmation de l'affectation facture par facture du mouvement bancaire ou de la remise deja identifiee.",
"accounting_support_needed": "Support externe ou explication documentee de l'ecriture comptable: banque, factor, compensation, virement interne ou cloture non cash.",
"missing_evidence_needed": "Document necessaire pour transformer l'indice en preuve concluante.",
"open_balance_confirmation": "Confirmation que le poste etait effectivement ouvert au {cutoff}, ou preuve ponctuelle d'une cloture non detectee.",
"unresolved": "Reference source ou preuve ponctuelle de cloture non detectee.",
},
"actions": {
"reconciled_strong": "Aucune demande operationnelle; conserver la reference dans le workpaper.",
"probable_payment": "Confirmer ou corriger l'affectation proposee, avec montant affecte, remise/SEPA et date de paiement par facture.",
"accounting_support_needed": "Envoyer seulement le support de contrepartie ou expliquer la nature de la cloture non cash; ne pas renvoyer grand livre/journal inchanges.",
"missing_evidence_needed": "Indiquer quel document deja acquis cloture la ligne, ou fournir uniquement le document manquant indique.",
"open_balance_confirmation": "Si ouvert: confirmer le solde. Si cloture: indiquer document de paiement/encaissement/factor/compensation et date.",
"unresolved": "Fournir la reference facture/auxiliaire ou indiquer si la ligne est erronee; si cloturee, joindre le document de cloture.",
},
"instructions": {
"no_reask": "Ne pas redemander les documents deja obtenus",
"no_reask_body": "Le workpaper principal contient deja postes ouverts, grands livres, journal, releves bancaires/factor, remises et normalisations disponibles.",
"targeted": "Demande ciblee",
"targeted_body": "Pour chaque ligne, demander uniquement l'element manquant indique: affectation, support externe, confirmation du solde ouvert ou reference source.",
"owner": "Destinataire",
"owner_body": "{entity} doit d'abord confirmer/corriger avec le dossier. {counterparty} sert seulement pour une eventuelle confirmation de solde ou reconciliation externe au {cutoff}.",
},
"log_path": "Workbook demandes ciblees: {path}",
},
"de": {
"sheet_names": {
"instructions": "anweisungen",
"summary": "zusammenfassung",
"inventory": "verfuegbare_evidenzen",
"reconciled_strong": "abgestimmt_stark",
"probable_payment": "wahrscheinliche_zahlungen",
"accounting_support_needed": "buchungen_zu_belegen",
"missing_evidence_needed": "evidenzen_ergaenzen",
"open_balance_confirmation": "offene_salden",
"unresolved": "nicht_geloest",
},
"headers": {
"principle": "prinzip",
"guidance": "hinweis",
"section": "abschnitt",
"rows": "zeilen",
"amount_total": "betrag_summe",
"evidence_category": "evidenz_kategorie",
"normalized_rows": "normalisierte_zeilen",
"source_files": "quelldateien",
"operational_use": "operative_verwendung",
"row_id": "workpaper_zeilen_id",
"side": "seite",
"document": "dokument",
"document_date": "dokumentdatum",
"amount": "betrag",
"operational_category": "operative_kategorie",
"operational_criterion": "operatives_kriterium",
"operational_owner": "operativer_empfaenger",
"available_evidence": "bereits_verfuegbare_evidenz",
"targeted_missing_item": "gezielt_fehlendes_element",
"existing_reference": "bestehende_referenz",
"requested_action": "angeforderte_aktion",
"evidence_description": "evidenz_beschreibung",
},
"sections": {
"reconciled_strong": "Abgestimmt mit starker Evidenz",
"probable_payment": "Wahrscheinliche Zahlungen zuzuordnen",
"accounting_support_needed": "Buchungen zu belegen",
"missing_evidence_needed": "Evidenzen zu ergaenzen",
"open_balance_confirmation": "Offene Salden zu bestaetigen",
"unresolved": "Nicht geloest",
},
"criteria": {
"reconciled_strong": "Externe Evidenz oder ausreichende Buchungs-Bank-Kette.",
"probable_payment": "Wahrscheinliche Bankbewegung oder Zahlungsliste, Zuordnung noch nicht bestaetigt.",
"accounting_support_needed": "Buchhalterische Ausbuchung ohne ausreichenden externen Beleg.",
"missing_evidence_needed": "Hinweis vorhanden, aber abschliessende Evidenz fehlt.",
"open_balance_confirmation": "Offener Saldo durch interne Evidenz gestuetzt.",
"unresolved": "Unzureichende Verbindung in den verfuegbaren Evidenzen.",
},
"side": {
"customer": "kunde",
"receivable": "kunde",
"client": "kunde",
"supplier": "lieferant",
"payable": "lieferant",
"vendor": "lieferant",
"unknown": "nicht angegeben",
},
"owners": {
"default_entity": "gepruefte einheit",
"default_counterparty": "gegenpartei",
"counterparty_confirmation": "{entity}; ggf. Saldobestaetigung {counterparty}",
},
"inventory_usage": {
"open_items": "Grundgesamtheit der offenen oder abzustimmenden Posten.",
"ledger": "Saldo-, Hauptbuch-, Nebenbuch- und interne Buchungsnachweise.",
"journal": "Buchungen, interne Ausbuchungen, Umbuchungen und Periodenbewegungen.",
"bank_statement": "Tatsaechliche Ein- und Auszahlungen laut Bankauszug.",
"payment_order": "Zahlungslisten oder Zahlungslaeufe zur Verbindung von Bank und Rechnungen.",
"factoring_statement": "Evidenz des Factors, Vorschusses oder externen Operators.",
"compensation_support": "Dokumentierter Aufrechnungs- oder Netting-Nachweis.",
"unknown": "Quelle erfasst, aber nicht automatisch klassifiziert.",
"default": "Im Dossier verfuegbare Evidenz.",
},
"available": {
"reconciled_strong": "Die Zeile ist im Workpaper bereits mit starker Evidenz abgestimmt.",
"probable_payment": "Bankauszug und wahrscheinlicher Match-Kandidat sind im Workpaper bereits vorhanden.",
"accounting_support_needed": "Offener Posten und Buchung sind bereits identifiziert.",
"missing_evidence_needed": "Hinweis oder Brueckenbeleg ist bereits identifiziert, aber nicht abschliessend.",
"open_balance_confirmation": "Offener Posten und interne Saldounterstuetzung sind bereits vorhanden.",
"unresolved": "Offener Posten vorhanden, ohne ausreichende Verbindung in den verfuegbaren Evidenzen.",
"reference": " Referenz: {reference}.",
},
"missing": {
"reconciled_strong": "Kein fehlendes Element fuer die gezielte Anfrage.",
"probable_payment": "Bestaetigung der rechnungsweisen Zuordnung der bereits identifizierten Bankbewegung oder Zahlungsliste.",
"accounting_support_needed": "Externer Beleg oder dokumentierte Erklaerung der Buchung: Bank, Factor, Aufrechnung, Umbuchung oder nicht zahlungswirksame Schliessung.",
"missing_evidence_needed": "Dokument, das den Hinweis in abschliessende Evidenz ueberfuehrt.",
"open_balance_confirmation": "Bestaetigung, dass der Posten am {cutoff} tatsaechlich offen war, oder konkreter Nachweis einer nicht erkannten Schliessung.",
"unresolved": "Quellreferenz oder konkreter Nachweis einer nicht erkannten Schliessung.",
},
"actions": {
"reconciled_strong": "Keine operative Anfrage; Referenz im Workpaper beibehalten.",
"probable_payment": "Vorgeschlagene Zuordnung bestaetigen oder korrigieren, je Rechnung mit zugeordnetem Betrag, Zahlungsliste/SEPA und Zahlungsdatum.",
"accounting_support_needed": "Nur den Gegenbeleg senden oder die nicht zahlungswirksame Schliessung erklaeren; unveraendertes Hauptbuch/Journal nicht erneut senden.",
"missing_evidence_needed": "Angeben, welches bereits vorliegende Dokument die Zeile schliesst, oder nur das angeforderte fehlende Dokument liefern.",
"open_balance_confirmation": "Wenn offen: Saldo bestaetigen. Wenn geschlossen: Zahlungs-/Einzugs-/Factor-/Aufrechnungsdokument und Datum angeben.",
"unresolved": "Rechnungs-/Nebenbuchreferenz liefern oder angeben, ob die Zeile falsch ist; falls geschlossen, Schliessungsbeleg beifuegen.",
},
"instructions": {
"no_reask": "Bereits erhaltene Dokumente nicht erneut anfordern",
"no_reask_body": "Das Haupt-Workpaper enthaelt bereits offene Posten, Hauptbuecher, Journal, Bank-/Factor-Ausweise, Zahlungslisten und verfuegbare Normalisierungen.",
"targeted": "Gezielte Anfrage",
"targeted_body": "Je Zeile nur das angegebene fehlende Element anfordern: Zuordnung, externer Beleg, Bestaetigung offener Saldo oder Quellreferenz.",
"owner": "Empfaenger",
"owner_body": "{entity} soll zuerst anhand des Dossiers bestaetigen/korrigieren. {counterparty} wird nur fuer eine moegliche Saldobestaetigung oder externe Abstimmung zum {cutoff} benoetigt.",
},
"log_path": "Workbook gezielte Anfragen: {path}",
},
"es": {
"sheet_names": {
"instructions": "instrucciones",
"summary": "resumen",
"inventory": "evidencias_disponibles",
"reconciled_strong": "conciliadas_prueba_fuerte",
"probable_payment": "pagos_probables",
"accounting_support_needed": "asientos_por_justificar",
"missing_evidence_needed": "evidencias_por_completar",
"open_balance_confirmation": "saldos_abiertos",
"unresolved": "sin_resolver",
},
"headers": {
"principle": "principio",
"guidance": "indicación",
"section": "sección",
"rows": "líneas",
"amount_total": "importe_total",
"evidence_category": "categoría_evidencia",
"normalized_rows": "líneas_normalizadas",
"source_files": "archivos_fuente",
"operational_use": "uso_operativo",
"row_id": "id_línea_papel_trabajo",
"side": "lado",
"document": "documento",
"document_date": "fecha_documento",
"amount": "importe",
"operational_category": "categoría_operativa",
"operational_criterion": "criterio_operativo",
"operational_owner": "destinatario_operativo",
"available_evidence": "evidencia_disponible",
"targeted_missing_item": "elemento_pendiente_dirigido",
"existing_reference": "referencia_existente",
"requested_action": "acción_solicitada",
"evidence_description": "descripción_evidencia",
},
"sections": {
"reconciled_strong": "Conciliadas con evidencia sólida",
"probable_payment": "Pagos probables por asignar",
"accounting_support_needed": "Asientos contables por justificar",
"missing_evidence_needed": "Evidencias por completar",
"open_balance_confirmation": "Saldos abiertos por confirmar",
"unresolved": "Sin resolver",
},
"criteria": {
"reconciled_strong": "Evidencia externa o cadena contable-bancaria suficiente.",
"probable_payment": "Movimiento bancario o remesa probable cuya asignación todavía no está confirmada.",
"accounting_support_needed": "Asiento contable de cierre sin respaldo externo suficiente.",
"missing_evidence_needed": "Existe un indicio, pero falta evidencia concluyente.",
"open_balance_confirmation": "Saldo abierto respaldado por evidencia interna.",
"unresolved": "Vínculo insuficiente en las evidencias disponibles.",
},
"side": {
"customer": "cliente",
"receivable": "cliente",
"client": "cliente",
"supplier": "proveedor",
"payable": "proveedor",
"vendor": "proveedor",
"unknown": "no indicado",
},
"owners": {
"default_entity": "entidad auditada",
"default_counterparty": "contraparte",
"counterparty_confirmation": "{entity}; posible confirmación del saldo por {counterparty}",
},
"inventory_usage": {
"open_items": "Población de partidas abiertas o por conciliar.",
"ledger": "Respaldo de saldos, mayores, auxiliares y registros internos.",
"journal": "Asientos contables, cierres internos, traspasos y movimientos del periodo.",
"bank_statement": "Cobros y pagos efectivos según el extracto bancario.",
"payment_order": "Órdenes o lotes de pago útiles para vincular bancos y facturas.",
"factoring_statement": "Evidencia del factor, anticipo u operador externo.",
"compensation_support": "Respaldo documentado de compensación o netting.",
"unknown": "Fuente adquirida, pero no clasificada automáticamente.",
"default": "Evidencia disponible en el expediente.",
},
"available": {
"reconciled_strong": "La línea ya está conciliada en el papel de trabajo con evidencia sólida.",
"probable_payment": "El extracto bancario y el candidato de coincidencia ya constan en el papel de trabajo.",
"accounting_support_needed": "La partida abierta y el asiento contable ya están identificados.",
"missing_evidence_needed": "Ya se identificó un indicio o documento puente, pero no es concluyente.",
"open_balance_confirmation": "La partida abierta y el respaldo interno del saldo ya están disponibles.",
"unresolved": "La partida abierta está disponible, pero no existe un vínculo suficiente en las evidencias adquiridas.",
"reference": " Referencia: {reference}.",
},
"missing": {
"reconciled_strong": "No falta ningún elemento para la solicitud dirigida.",
"probable_payment": "Confirmación de la asignación factura por factura del movimiento bancario o del lote de pagos ya identificado.",
"accounting_support_needed": "Respaldo externo o explicación documentada del asiento contable: banco, factor, compensación, traspaso o cierre sin efectivo.",
"missing_evidence_needed": "Documento necesario para convertir el indicio en evidencia concluyente.",
"open_balance_confirmation": "Confirmación de que la partida seguía abierta a {cutoff}, o evidencia concreta de un cierre no detectado.",
"unresolved": "Referencia fuente o evidencia concreta de un cierre no detectado.",
},
"actions": {
"reconciled_strong": "No se requiere ninguna solicitud operativa; conserve la referencia en el papel de trabajo.",
"probable_payment": "Confirme o corrija la asignación propuesta e indique el importe asignado, el lote o SEPA y la fecha de pago de cada factura.",
"accounting_support_needed": "Envíe únicamente el justificante de contrapartida o explique el cierre sin efectivo; no vuelva a enviar el mayor o el diario si no han cambiado.",
"missing_evidence_needed": "Indique qué documento ya adquirido cierra la línea o proporcione únicamente el documento pendiente indicado.",
"open_balance_confirmation": "Si está abierta, confirme el saldo. Si está cerrada, indique el documento de pago, cobro, factor o compensación y la fecha.",
"unresolved": "Proporcione la referencia de la factura o del auxiliar, o indique si la línea es errónea; si está cerrada, adjunte el documento de cierre.",
},
"instructions": {
"no_reask": "No vuelva a solicitar documentos ya adquiridos",
"no_reask_body": "El papel de trabajo principal ya contiene las partidas abiertas, los mayores, el diario, los extractos bancarios o del factor, los lotes de pagos y las normalizaciones disponibles.",
"targeted": "Solicitud dirigida",
"targeted_body": "Para cada línea, solicite solo el elemento pendiente indicado: asignación, respaldo externo, confirmación de saldo abierto o referencia fuente.",
"owner": "Destinatario",
"owner_body": "{entity} debe confirmar o corregir primero con el expediente. {counterparty} solo se necesita para una posible confirmación de saldo o conciliación externa a {cutoff}.",
},
"log_path": "Libro de solicitudes dirigidas: {path}",
},
"en": {
"sheet_names": {
"instructions": "instructions",
"summary": "summary",
"inventory": "available_evidence",
"reconciled_strong": "reconciled_strong",
"probable_payment": "probable_payments",
"accounting_support_needed": "accounting_support_needed",
"missing_evidence_needed": "evidence_to_complete",
"open_balance_confirmation": "open_balances",
"unresolved": "unresolved",
},
"headers": {
"principle": "principle",
"guidance": "guidance",
"section": "section",
"rows": "rows",
"amount_total": "amount_total",
"evidence_category": "evidence_category",
"normalized_rows": "normalized_rows",
"source_files": "source_files",
"operational_use": "operational_use",
"row_id": "workpaper_row_id",
"side": "side",
"document": "document",
"document_date": "document_date",
"amount": "amount",
"operational_category": "operational_category",
"operational_criterion": "operational_criterion",
"operational_owner": "operational_owner",
"available_evidence": "available_evidence",
"targeted_missing_item": "targeted_missing_item",
"existing_reference": "existing_reference",
"requested_action": "requested_action",
"evidence_description": "evidence_description",
},
"sections": {
"reconciled_strong": "Reconciled with strong evidence",
"probable_payment": "Probable payments to allocate",
"accounting_support_needed": "Accounting entries to support",
"missing_evidence_needed": "Evidence to complete",
"open_balance_confirmation": "Open balances to confirm",
"unresolved": "Unresolved",
},
"criteria": {
"reconciled_strong": "External evidence or sufficient accounting-bank chain.",
"probable_payment": "Probable bank movement or batch, allocation not yet confirmed.",
"accounting_support_needed": "Accounting closing entry without sufficient external support.",
"missing_evidence_needed": "An indicator exists but conclusive evidence is missing.",
"open_balance_confirmation": "Open balance supported by internal evidence.",
"unresolved": "Insufficient link in available evidence.",
},
"side": {
"customer": "customer",
"receivable": "customer",
"client": "customer",
"supplier": "supplier",
"payable": "supplier",
"vendor": "supplier",
"unknown": "not specified",
},
"owners": {
"default_entity": "audited entity",
"default_counterparty": "counterparty",
"counterparty_confirmation": "{entity}; possible balance confirmation from {counterparty}",
},
"inventory_usage": {
"open_items": "Open-item or disputed population.",
"ledger": "Balance, subledger, ledger and internal posting support.",
"journal": "Accounting entries, internal closings, transfers and period movements.",
"bank_statement": "Actual receipts and payments from bank statement.",
"payment_order": "Payment orders or batches used to connect bank and invoices.",
"factoring_statement": "Factoring, advance or external operator evidence.",
"compensation_support": "Documented set-off or netting support.",
"unknown": "Source acquired but not automatically classified.",
"default": "Evidence acquired in the file.",
},
"available": {
"reconciled_strong": "The row is already reconciled in the workpaper with strong evidence.",
"probable_payment": "Bank statement and match candidate already exist in the workpaper.",
"accounting_support_needed": "Open item and accounting entry already identified.",
"missing_evidence_needed": "Bridge document or indicator already identified, but not conclusive.",
"open_balance_confirmation": "Open item and internal balance support already exist.",
"unresolved": "Open item exists, with no sufficient link in the acquired evidence.",
"reference": " Reference: {reference}.",
},
"missing": {
"reconciled_strong": "No missing item for the targeted request.",
"probable_payment": "Invoice-by-invoice allocation confirmation for the bank movement or payment batch already identified.",
"accounting_support_needed": "External support or documented explanation for the accounting entry: bank, factor, set-off, transfer or non-cash closing.",
"missing_evidence_needed": "Document required to turn the indicator into conclusive evidence.",
"open_balance_confirmation": "Confirmation that the item was still open at {cutoff}, or specific evidence of an undetected closing.",
"unresolved": "Source reference or specific evidence of an undetected closing.",
},
"actions": {
"reconciled_strong": "No operational request; retain the reference in the workpaper.",
"probable_payment": "Confirm or correct the proposed allocation, with allocated amount, batch/SEPA and payment date for each invoice.",
"accounting_support_needed": "Send only the counterparty support or explain the non-cash closing; do not resend unchanged ledger/journal.",
"missing_evidence_needed": "Indicate which already-acquired document closes the row, or provide only the missing document requested.",
"open_balance_confirmation": "If open: confirm the balance. If closed: indicate payment/receipt/factor/set-off document and date.",
"unresolved": "Provide invoice/subledger reference or indicate if the row is wrong; if closed, attach the closing document.",
},
"instructions": {
"no_reask": "Do not request documents already acquired",
"no_reask_body": "The main workpaper already contains available open items, ledgers, journal, bank/factor statements, payment batches and normalizations.",
"targeted": "Targeted request",
"targeted_body": "For each row, request only the indicated missing item: allocation, external support, open-balance confirmation or source reference.",
"owner": "Recipient",
"owner_body": "{entity} should first confirm/correct using the file. {counterparty} is only for possible balance confirmation or external reconciliation at {cutoff}.",
},
"log_path": "Targeted request workbook: {path}",
},
}
@dataclass(frozen=True)
class MissingEvidenceRequestPack:
"""Targeted evidence requests and the source inventory behind them."""
language: str
instructions: list[dict[str, Any]]
summary: list[dict[str, Any]]
evidence_inventory: list[dict[str, Any]]
request_sections: dict[str, list[dict[str, Any]]]
def clean_text(value: object) -> str:
return " ".join(str(value or "").strip().split())
def text_for(language: object) -> dict[str, Any]:
code = normalize_language(language)
return TEXT.get(code, TEXT["en"])
def parse_decimal(value: object) -> Decimal:
text = clean_text(value).replace(" ", "")
if not text:
return Decimal("0.00")
if "," in text and "." in text:
text = text.replace(".", "").replace(",", ".")
else:
text = text.replace(",", ".")
try:
return Decimal(text)
except InvalidOperation:
return Decimal("0.00")
def format_decimal(value: object) -> str:
return f"{parse_decimal(value):.2f}"
def side_label(value: object, language: object) -> str:
side = clean_text(value).lower()
labels = text_for(language)["side"]
return labels.get(side, labels["unknown"])
def load_sheet_rows(
workbook_path: str | Path,
sheet_name: str,
*,
retain_workbook_row: bool = False,
) -> list[dict[str, Any]]:
workbook = load_workbook(workbook_path, read_only=True, data_only=True)
if sheet_name not in workbook.sheetnames:
return []
sheet = workbook[sheet_name]
header_row = next(sheet.iter_rows(min_row=1, max_row=1), None)
if header_row is None:
return []
headers = [clean_text(cell.value) for cell in header_row]
rows: list[dict[str, Any]] = []
for workbook_row, values in enumerate(
sheet.iter_rows(min_row=2, values_only=True), start=2
):
if not any(value not in (None, "") for value in values):
continue
row = dict(zip(headers, values))
if retain_workbook_row:
row["workbook_row"] = workbook_row
rows.append(row)
return rows
def load_reconciliation_context(
workbook_path: str | Path,
) -> dict[str, list[dict[str, Any]]]:
"""Load the standard sheets needed for targeted evidence requests."""
return {
"reconciliation_rows": load_sheet_rows(
workbook_path, "Reconciliation detail", retain_workbook_row=True
),
"source_inventory": load_sheet_rows(workbook_path, "Source inventory"),
"normalized_records": load_sheet_rows(workbook_path, "Normalized records"),
"post_cutoff_candidates": load_sheet_rows(
workbook_path, "Post-cutoff candidates"
),
}
def source_files_by_role(
source_inventory: list[dict[str, Any]],
normalized_records: list[dict[str, Any]],
) -> dict[str, set[str]]:
files_by_role: dict[str, set[str]] = {}
for row in [*source_inventory, *normalized_records]:
role = clean_text(row.get("source_role"))
source_file = clean_text(row.get("source_file"))
if role and source_file:
files_by_role.setdefault(role, set()).add(source_file)
return files_by_role
def record_counts_by_role(normalized_records: list[dict[str, Any]]) -> dict[str, int]:
counts: dict[str, int] = {}
for row in normalized_records:
role = clean_text(row.get("source_role")) or "unknown"
counts[role] = counts.get(role, 0) + 1
return counts
def evidence_usage(role: str, language: object) -> str:
inventory_usage = text_for(language)["inventory_usage"]
return inventory_usage.get(role, inventory_usage["default"])
def evidence_inventory_rows(
source_inventory: list[dict[str, Any]],
normalized_records: list[dict[str, Any]],
*,
language: object,
) -> list[dict[str, Any]]:
files_by_role = source_files_by_role(source_inventory, normalized_records)
counts_by_role = record_counts_by_role(normalized_records)
roles = sorted(set(files_by_role) | set(counts_by_role))
return [
{
"evidence_category": evidence_usage(role, language).split(".", 1)[0],
"normalized_rows": counts_by_role.get(role, 0),
"source_files": "; ".join(sorted(files_by_role.get(role, set()))),
"operational_use": evidence_usage(role, language),
}
for role in roles
]
def operational_owner(
section: str,
*,
entity_name: str,
counterparty_name: str,
language: object,
) -> str:
texts = text_for(language)
owners = texts["owners"]
entity = clean_text(entity_name) or owners["default_entity"]
counterparty = clean_text(counterparty_name) or owners["default_counterparty"]
if section == "open_balance_confirmation":
return owners["counterparty_confirmation"].format(
entity=entity, counterparty=counterparty
)
return entity
def section_for_row(row: dict[str, Any]) -> str:
status = clean_text(row.get("reconciliation_status"))
rule = clean_text(row.get("rule_applied"))
if status == "closed":
return "reconciled_strong"
if status == "probable_payment":
return "probable_payment"
if status == "needs_evidence" and rule == "internal_closure_without_external":
return "accounting_support_needed"
if status in {"needs_evidence", "partially_paid"}:
return "missing_evidence_needed"
if status == "open_supported":
return "open_balance_confirmation"
if status == "unresolved":
return "unresolved"
return ""
def existing_reference(row: dict[str, Any]) -> str:
for field in (
"probable_bank_reference",
"matched_evidence_reference",
"supporting_bank_reference",
"evidence_reference",
"source_reference",
):
value = clean_text(row.get(field))
if value:
return value
source_file = clean_text(row.get("source_file"))
workbook_row = clean_text(row.get("workbook_row"))
if source_file and workbook_row:
return f"{source_file}; workpaper row {workbook_row}"
return source_file
def existing_description(row: dict[str, Any]) -> str:
for field in (
"probable_bank_description",
"matched_evidence_description",
"supporting_bank_description",
"evidence_description",
"description",
):
value = clean_text(row.get(field))
if value:
return value
return ""
def available_text(
section: str,
row: dict[str, Any],
*,
language: object,
) -> str:
texts = text_for(language)
available = texts["available"][section]
reference = existing_reference(row)
if reference:
available += texts["available"]["reference"].format(reference=reference)
return available
def missing_text(
section: str,
*,
cutoff_date: object,
language: object,
) -> str:
cutoff = clean_text(cutoff_date) or "cut-off"
return text_for(language)["missing"][section].format(cutoff=cutoff)
def requested_action(section: str, *, language: object) -> str:
return text_for(language)["actions"][section]
def request_row(
row: dict[str, Any],
*,
section: str,
entity_name: str,
counterparty_name: str,
cutoff_date: object,
language: object,
) -> dict[str, Any]:
texts = text_for(language)
result = {
"row_id": clean_text(row.get("record_id")),
"side": side_label(row.get("expected_side"), language),
"document": clean_text(row.get("document_no")),
"document_date": clean_text(row.get("document_date")),
"amount": format_decimal(row.get("amount")),
"operational_category": texts["sections"][section],
"operational_criterion": texts["criteria"][section],
"operational_owner": operational_owner(
section,
entity_name=entity_name,
counterparty_name=counterparty_name,
language=language,
),
"available_evidence": available_text(section, row, language=language),
"targeted_missing_item": missing_text(
section, cutoff_date=cutoff_date, language=language
),
"existing_reference": existing_reference(row),
"requested_action": requested_action(section, language=language),
"evidence_description": existing_description(row),
}
if clean_text(row.get("reconciliation_status")) == "partially_paid":
paid = format_decimal(row.get("allocated_amount"))
residual = format_decimal(row.get("residual_amount"))
partial_texts = {
"it": (
"Pagamento parziale documentato: {paid}.",
"Per il solo residuo di {residual}, acquisire evidenza del saldo oppure conferma che resta aperto alla data di riferimento.",
),
"en": (
"Documented partial payment: {paid}.",
"For the remaining {residual} only, obtain settlement evidence or confirmation that it remains open at the cut-off.",
),
"fr": (
"Paiement partiel documenté : {paid}.",
"Pour le seul solde de {residual}, obtenir une preuve de règlement ou la confirmation qu’il reste ouvert à la date de référence.",
),
"de": (
"Belegte Teilzahlung: {paid}.",
"Nur für den Restbetrag von {residual} einen Zahlungsnachweis oder die Bestätigung einholen, dass er am Stichtag noch offen ist.",
),
"es": (
"Pago parcial documentado: {paid}.",
"Solo para el saldo de {residual}, obtener evidencia de liquidación o confirmación de que sigue pendiente a la fecha de corte.",
),
}
available, action = partial_texts[normalize_language(language)]
result["amount"] = residual
result["available_evidence"] = available.format(paid=paid)
if reference := existing_reference(row):
result["available_evidence"] += texts["available"]["reference"].format(
reference=reference
)
result["targeted_missing_item"] = action.format(residual=residual)
result["requested_action"] = result["targeted_missing_item"]
if clean_text(row.get("rule_applied")) == "accounting_perimeter_mismatch":
actions = {
"it": "Il candidato non rispetta il perimetro verificato (soggetto, controparte, valuta o unità). Confermare i dati delle due fonti e fornire il riscontro documentale necessario; importi numericamente uguali non dimostrano la chiusura.",
"en": "The candidate does not share the reviewed entity, party, currency or unit. Confirm both source records and provide the necessary documentary support; numerically equal amounts do not establish settlement.",
"fr": "Le candidat ne respecte pas le périmètre vérifié (entité, contrepartie, devise ou unité). Confirmer les deux sources et fournir le justificatif nécessaire ; des montants numériquement égaux ne prouvent pas le règlement.",
"de": "Der Kandidat entspricht nicht dem geprüften Umfang (Unternehmen, Gegenpartei, Währung oder Einheit). Beide Quellen bestätigen und erforderliche Belege vorlegen; numerisch gleiche Beträge beweisen keine Zahlung.",
"es": "El candidato no comparte el perímetro revisado (entidad, contraparte, moneda o unidad). Confirmar ambas fuentes y aportar el soporte necesario; importes numéricamente iguales no demuestran la liquidación.",
}
result["targeted_missing_item"] = actions[normalize_language(language)]
result["requested_action"] = result["targeted_missing_item"]
return result
def build_instructions(
*,
entity_name: str,
counterparty_name: str,
cutoff_date: object,
language: object,
) -> list[dict[str, Any]]:
texts = text_for(language)
instructions = texts["instructions"]
owners = texts["owners"]
entity = clean_text(entity_name) or owners["default_entity"]
counterparty = clean_text(counterparty_name) or owners["default_counterparty"]
cutoff = clean_text(cutoff_date) or "cut-off"
return [
{
"principle": instructions["no_reask"],
"guidance": instructions["no_reask_body"],
},
{
"principle": instructions["targeted"],
"guidance": instructions["targeted_body"],
},
{
"principle": instructions["owner"],
"guidance": instructions["owner_body"].format(
entity=entity, counterparty=counterparty, cutoff=cutoff
),
},
]
def build_summary(
request_sections: dict[str, list[dict[str, Any]]],
*,
language: object,
) -> list[dict[str, Any]]:
texts = text_for(language)
rows: list[dict[str, Any]] = []
for section in SECTION_ORDER:
section_rows = request_sections.get(section, [])
amount_total = sum(parse_decimal(row.get("amount")) for row in section_rows)
rows.append(
{
"section": texts["sections"][section],
"rows": len(section_rows),
"amount_total": f"{amount_total:.2f}",
}
)
return rows
def build_missing_evidence_request_pack(
reconciliation_rows: list[dict[str, Any]],
*,
source_inventory: list[dict[str, Any]] | None = None,
normalized_records: list[dict[str, Any]] | None = None,
post_cutoff_candidates: list[dict[str, Any]] | None = None,
entity_name: str = "",
counterparty_name: str = "",
cutoff_date: object = "",
language: object = "it",
) -> MissingEvidenceRequestPack:
"""Build a localized operational request pack from technical workpaper rows."""
output_language = normalize_language(language)
request_sections: dict[str, list[dict[str, Any]]] = {
section: [] for section in SECTION_ORDER
}
for row in reconciliation_rows:
section = section_for_row(row)
if not section:
continue
request = request_row(
row,
section=section,
entity_name=entity_name,
counterparty_name=counterparty_name,
cutoff_date=cutoff_date,
language=output_language,
)
later = [
candidate
for candidate in post_cutoff_candidates or []
if candidate.get("open_record_id") == row.get("record_id")
]
if later and section != "reconciled_strong":
templates = {
"it": (
"Evidenza candidata già acquisita successiva al cut-off: {details}.",
"Confermare il saldo al {cutoff}; l'evidenza successiva non dimostra la chiusura a quella data. Se si dichiara una chiusura precedente, indicare solo il relativo documento mancante.",
),
"en": (
"Later candidate evidence already held: {details}.",
"Confirm the balance at {cutoff}; later evidence does not establish settlement at that date. If earlier settlement is claimed, identify only its missing supporting document.",
),
"fr": (
"Élément candidat postérieur déjà disponible : {details}.",
"Confirmer le solde au {cutoff} ; cet élément postérieur ne prouve pas le règlement à cette date. Si un règlement antérieur est déclaré, indiquer uniquement son justificatif manquant.",
),
"de": (
"Bereits vorliegender späterer Belegkandidat: {details}.",
"Saldo zum {cutoff} bestätigen; der spätere Beleg beweist keine Zahlung zum Stichtag. Wird eine frühere Zahlung erklärt, nur den fehlenden Beleg dafür angeben.",
),
"es": (
"Evidencia candidata posterior ya disponible: {details}.",
"Confirmar el saldo al {cutoff}; la evidencia posterior no demuestra la liquidación a esa fecha. Si se declara una liquidación anterior, indicar solo su justificante pendiente.",
),
}
available, action = templates[output_language]
references = [
f"{clean_text(item.get('evidence_date'))}; {clean_text(item.get('evidence_source_file'))}; "
f"page={clean_text(item.get('evidence_source_page'))}; row={clean_text(item.get('evidence_source_row'))}; "
f"id={clean_text(item.get('evidence_record_id'))}"
for item in later
]
request["available_evidence"] += " " + available.format(
details=" | ".join(references)
)
request["existing_reference"] += " | " + " | ".join(references)
request["targeted_missing_item"] = action.format(
cutoff=clean_text(cutoff_date) or "cut-off"
)
request["requested_action"] = request["targeted_missing_item"]
request_sections[section].append(request)
return MissingEvidenceRequestPack(
language=output_language,
instructions=build_instructions(
entity_name=entity_name,
counterparty_name=counterparty_name,
cutoff_date=cutoff_date,
language=output_language,
),
summary=build_summary(request_sections, language=output_language),
evidence_inventory=evidence_inventory_rows(
source_inventory or [],
normalized_records or [],
language=output_language,
),
request_sections=request_sections,
)
def sheet_headers(
rows: list[dict[str, Any]],
fields: tuple[str, ...],
) -> list[str]:
headers = list(fields)
extras = sorted({key for row in rows for key in row if key not in fields})
headers.extend(extras)
return headers
def write_sheet(
workbook: Workbook,
name: str,
rows: list[dict[str, Any]],
fields: tuple[str, ...],
*,
language: object,
) -> None:
texts = text_for(language)
sheet = workbook.create_sheet(title=name[:31])
keys = sheet_headers(rows, fields)
display_headers = [texts["headers"].get(key, key) for key in keys]
sheet.append(display_headers)
for row in rows:
sheet.append([row.get(key, "") for key in keys])
fill = PatternFill(fill_type="solid", fgColor=HEADER_FILL)
for cell in sheet[1]:
cell.fill = fill
cell.font = Font(color="FFFFFF", bold=True)
cell.alignment = Alignment(wrap_text=True, vertical="top")
for column_cells in sheet.columns:
max_length = (
max(
len(clean_text(cell.value))
for cell in column_cells
if cell.value is not None
)
if any(cell.value is not None for cell in column_cells)
else 8
)
adjusted_width = min(max(max_length + 2, 10), 60)
sheet.column_dimensions[get_column_letter(column_cells[0].column)].width = (
adjusted_width
)
for cell in column_cells:
cell.alignment = Alignment(wrap_text=True, vertical="top")
sheet.freeze_panes = "A2"
def write_missing_evidence_workbook(
path: str | Path,
pack: MissingEvidenceRequestPack,
) -> Path:
"""Write the localized targeted request workbook."""
output_path = Path(path)
output_path.parent.mkdir(parents=True, exist_ok=True)
workbook = Workbook()
workbook.remove(workbook.active)
texts = text_for(pack.language)
sheet_names = texts["sheet_names"]
write_sheet(
workbook,
sheet_names["instructions"],
pack.instructions,
INSTRUCTION_FIELDS,
language=pack.language,
)
write_sheet(
workbook,
sheet_names["summary"],
pack.summary,
SUMMARY_FIELDS,
language=pack.language,
)
write_sheet(
workbook,
sheet_names["inventory"],
pack.evidence_inventory,
INVENTORY_FIELDS,
language=pack.language,
)
for section in SECTION_ORDER:
write_sheet(
workbook,
sheet_names[section],
pack.request_sections.get(section, []),
REQUEST_FIELDS,
language=pack.language,
)
workbook.save(output_path)
return output_path
def default_output_dir(workbook_path: str | Path) -> Path:
return Path(workbook_path).resolve().parent
def main(argv: list[str] | None = None) -> int:
parser = argparse.ArgumentParser(
description="Build localized targeted evidence requests from a reconciliation workbook."
)
parser.add_argument("workbook", type=Path)
parser.add_argument("--output-dir", type=Path)
parser.add_argument("--output-name", default=DEFAULT_EXCEL_NAME)
parser.add_argument("--entity-name", default="")
parser.add_argument("--counterparty-name", default="")
parser.add_argument("--cutoff-date", default="")
parser.add_argument("--language", default="it")
parser.add_argument("--client-engagement", type=Path, required=True)
args = parser.parse_args(argv)
logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")
language = normalize_language(args.language)
output_dir = args.output_dir or default_output_dir(args.workbook)
output_path = output_dir / args.output_name
try:
load_client_engagement_context_file(
args.client_engagement,
expected_workflow_id="open-item-reconciliation",
input_paths=[args.workbook],
output_dir=output_path,
)
except AssuranceContractError as exc:
parser.error(str(exc))
context = load_reconciliation_context(args.workbook)
pack = build_missing_evidence_request_pack(
context["reconciliation_rows"],
source_inventory=context["source_inventory"],
normalized_records=context["normalized_records"],
post_cutoff_candidates=context["post_cutoff_candidates"],
entity_name=args.entity_name,
counterparty_name=args.counterparty_name,
cutoff_date=args.cutoff_date,
language=language,
)
output_path = write_missing_evidence_workbook(output_path, pack)
LOGGER.info(text_for(language)["log_path"].format(path=output_path))
for row in pack.summary:
LOGGER.info(
"%s: %s / %s",
row["section"],
row["rows"],
row["amount_total"],
)
return 0
if __name__ == "__main__":
raise SystemExit(main())
SHA-256: a7a8bb44aca8d25c6d4ad4d1b447db9d205fc2c6873e990a7f4b8d1e65aa49ec