===== 1. Factures citees ===== SELECT f.rowid,f.ref,f.entity,f.type,f.fk_statut,f.paye,f.close_code,f.datef,f.total_ht,f.total_tva,f.total_ttc,s.nom FROM llx_facture f LEFT JOIN llx_societe s ON s.rowid=f.fk_soc WHERE f.rowid IN (39,73) OR f.ref IN ('FA2509-0033','FA2510-0067') ----- rowid=39 | ref=FA2509-0033 | entity=1 | type=0 | fk_statut=2 | paye=1 | close_code= | datef=2025-09-09 | total_ht=4563.34000000 | total_tva=912.67000000 | total_ttc=5476.01000000 | nom=SCIADSIMMO rowid=73 | ref=FA2510-0067 | entity=1 | type=0 | fk_statut=2 | paye=1 | close_code= | datef=2025-10-19 | total_ht=2965.87000000 | total_tva=593.19000000 | total_ttc=3559.06000000 | nom=Normandie Biologi'K Construction (2 lignes) ===== 2. Paiements des 2 factures ===== SELECT f.ref,pf.rowid AS pfid,pf.fk_paiement,p.ref AS payref,p.datep,p.amount AS pay_amount,pf.amount AS impute,pf.multicurrency_amount,p.fk_bank FROM llx_facture f JOIN llx_paiement_facture pf ON pf.fk_facture=f.rowid JOIN llx_paiement p ON p.rowid=pf.fk_paiement WHERE f.rowid IN (39,73) ORDER BY f.ref,p.datep ----- ref=FA2509-0033 | pfid=23 | fk_paiement=7 | payref=PAY2509-0007 | datep=2025-09-19 12:00:00 | pay_amount=5476.03000000 | impute=5476.03000000 | multicurrency_amount=5476.03000000 | fk_bank=8 ref=FA2510-0067 | pfid=51 | fk_paiement=23 | payref=PAY2510-0023 | datep=2025-10-23 12:00:00 | pay_amount=8884.06000000 | impute=3559.04000000 | multicurrency_amount=3559.04000000 | fk_bank=24 (2 lignes) ===== 3. Remises/avoirs imputes ===== SELECT rowid,fk_soc,fk_facture,fk_facture_line,fk_facture_source,amount_ht,amount_tva,amount_ttc,description FROM llx_societe_remise_except WHERE fk_facture IN (39,73) OR fk_facture_source IN (39,73) ----- (0 lignes) ===== 4. Factures marquees payees avec ecart de reglement ===== SELECT f.rowid,f.ref,f.datef,f.paye,f.fk_statut,f.close_code,f.total_ttc, COALESCE(pp.tot,0) AS paye_total, COALESCE(av.tot,0) AS avoirs, ROUND(f.total_ttc - COALESCE(pp.tot,0) - COALESCE(av.tot,0),2) AS reste FROM llx_facture f LEFT JOIN (SELECT fk_facture,SUM(amount) tot FROM llx_paiement_facture GROUP BY fk_facture) pp ON pp.fk_facture=f.rowid LEFT JOIN (SELECT fk_facture,SUM(amount_ttc) tot FROM llx_societe_remise_except WHERE fk_facture IS NOT NULL GROUP BY fk_facture) av ON av.fk_facture=f.rowid WHERE f.entity=1 AND f.paye=1 AND f.fk_statut=2 AND ABS(f.total_ttc - COALESCE(pp.tot,0) - COALESCE(av.tot,0)) > 0.001 ORDER BY ABS(f.total_ttc - COALESCE(pp.tot,0) - COALESCE(av.tot,0)) DESC ----- rowid=73 | ref=FA2510-0067 | datef=2025-10-19 | paye=1 | fk_statut=2 | close_code= | total_ttc=3559.06000000 | paye_total=3559.04000000 | avoirs=0.00000000 | reste=0.02 rowid=39 | ref=FA2509-0033 | datef=2025-09-09 | paye=1 | fk_statut=2 | close_code= | total_ttc=5476.01000000 | paye_total=5476.03000000 | avoirs=0.00000000 | reste=-0.02 (2 lignes) ===== 5. Petits ecarts de reglement (<1 EUR) toutes factures ===== SELECT f.rowid,f.ref,f.paye,f.fk_statut,f.type,f.total_ttc,COALESCE(pp.tot,0) AS paye_total, ROUND(f.total_ttc-COALESCE(pp.tot,0),2) AS ecart FROM llx_facture f LEFT JOIN (SELECT fk_facture,SUM(amount) tot FROM llx_paiement_facture GROUP BY fk_facture) pp ON pp.fk_facture=f.rowid WHERE f.entity=1 AND COALESCE(pp.tot,0)>0 AND ABS(f.total_ttc-COALESCE(pp.tot,0))>0.001 AND ABS(f.total_ttc-COALESCE(pp.tot,0))<1 ORDER BY f.rowid ----- rowid=39 | ref=FA2509-0033 | paye=1 | fk_statut=2 | type=0 | total_ttc=5476.01000000 | paye_total=5476.03000000 | ecart=-0.02 rowid=73 | ref=FA2510-0067 | paye=1 | fk_statut=2 | type=0 | total_ttc=3559.06000000 | paye_total=3559.04000000 | ecart=0.02 (2 lignes) ===== 6. Lignes facture arithmetiquement fausses (seuil >0.02) ===== SELECT d.rowid,f.ref,f.entity,d.qty,d.subprice,d.remise_percent,d.total_ht, ROUND(d.qty*d.subprice*(1-d.remise_percent/100),2) AS calc, ROUND(d.total_ht-ROUND(d.qty*d.subprice*(1-d.remise_percent/100),2),2) AS delta, d.description FROM llx_facturedet d JOIN llx_facture f ON f.rowid=d.fk_facture WHERE f.entity=1 AND ABS(d.total_ht-ROUND(d.qty*d.subprice*(1-d.remise_percent/100),2))>0.02 ORDER BY ABS(d.total_ht-ROUND(d.qty*d.subprice*(1-d.remise_percent/100),2)) DESC ----- rowid=1184 | ref=FA2605-0212 | entity=1 | qty=16 | subprice=36.10000000 | remise_percent=0 | total_ht=577.54000000 | calc=577.6 | delta=-0.06 | description=ALU 15/10 Satiné - RAL 7016 - DEV:270mm LONG:3000mm (3 plis) EXT rowid=340 | ref=FA2510-0067 | entity=1 | qty=7 | subprice=27.85000000 | remise_percent=0 | total_ht=194.98000000 | calc=194.95 | delta=0.03 | description=ALU Prefa 70/100 Gris sombre - DEV:166mm LONG:3000mm (2 lignes) ===== 7. Ampleur des ecarts de ligne ===== SELECT COUNT(*) AS nb_lignes_total, SUM(CASE WHEN ABS(d.total_ht-ROUND(d.qty*d.subprice*(1-d.remise_percent/100),2))>0.005 THEN 1 ELSE 0 END) AS nb_ecart_sup_0_005, SUM(CASE WHEN ABS(d.total_ht-ROUND(d.qty*d.subprice*(1-d.remise_percent/100),2))>0.02 THEN 1 ELSE 0 END) AS nb_ecart_sup_0_02 FROM llx_facturedet d JOIN llx_facture f ON f.rowid=d.fk_facture WHERE f.entity=1 ----- nb_lignes_total=1887 | nb_ecart_sup_0_005=28 | nb_ecart_sup_0_02=2 (1 lignes) ===== 8. Lignes 340 et 1184 ===== SELECT d.rowid,f.ref,d.fk_facture,d.qty,d.subprice,d.remise_percent,d.total_ht,d.total_tva,d.total_ttc,d.tva_tx,d.fk_product,d.product_type,d.description FROM llx_facturedet d JOIN llx_facture f ON f.rowid=d.fk_facture WHERE d.rowid IN (340,1184) ----- rowid=340 | ref=FA2510-0067 | fk_facture=73 | qty=7 | subprice=27.85000000 | remise_percent=0 | total_ht=194.98000000 | total_tva=39.00000000 | total_ttc=233.98000000 | tva_tx=20.0000 | fk_product= | product_type=0 | description=ALU Prefa 70/100 Gris sombre - DEV:166mm LONG:3000mm rowid=1184 | ref=FA2605-0212 | fk_facture=234 | qty=16 | subprice=36.10000000 | remise_percent=0 | total_ht=577.54000000 | total_tva=115.51000000 | total_ttc=693.05000000 | tva_tx=20.0000 | fk_product= | product_type=0 | description=ALU 15/10 Satiné - RAL 7016 - DEV:270mm LONG:3000mm (3 plis) EXT (2 lignes) ===== 9. Totaux facture vs somme des lignes ===== SELECT f.rowid,f.ref,f.total_ht,SUM(d.total_ht) AS somme_lignes,ROUND(f.total_ht-SUM(d.total_ht),2) AS ecart FROM llx_facture f JOIN llx_facturedet d ON d.fk_facture=f.rowid WHERE f.rowid IN (39,73,(SELECT fk_facture FROM llx_facturedet WHERE rowid=1184)) GROUP BY f.rowid,f.ref,f.total_ht ----- rowid=39 | ref=FA2509-0033 | total_ht=4563.34000000 | somme_lignes=4563.34000000 | ecart=0 rowid=73 | ref=FA2510-0067 | total_ht=2965.87000000 | somme_lignes=2965.87000000 | ecart=0 rowid=234 | ref=FA2605-0212 | total_ht=613.92000000 | somme_lignes=613.92000000 | ecart=0 (3 lignes) ===== 10. Ecritures bancaires 8 et 24 ===== SELECT b.rowid,b.dateo,b.datev,b.amount,b.label,b.num_releve,b.rappro,b.fk_account FROM llx_bank b WHERE b.rowid IN (8,24) ----- rowid=8 | dateo=2025-09-19 | datev=2025-09-19 | amount=5476.03000000 | label=(CustomerInvoicePayment) | num_releve= | rappro=0 | fk_account=2 rowid=24 | dateo=2025-10-23 | datev=2025-10-23 | amount=8884.06000000 | label=(CustomerInvoicePayment) | num_releve= | rappro=0 | fk_account=2 (2 lignes) ===== 11. Paiements cites : imputation totale ===== SELECT p.rowid,p.ref,p.datep,p.amount AS montant_paiement,p.fk_bank, (SELECT SUM(amount) FROM llx_paiement_facture WHERE fk_paiement=p.rowid) AS somme_imputee, (SELECT COUNT(*) FROM llx_paiement_facture WHERE fk_paiement=p.rowid) AS nb_factures FROM llx_paiement p WHERE p.ref IN ('PAY2509-0007','PAY2510-0023') ----- rowid=7 | ref=PAY2509-0007 | datep=2025-09-19 12:00:00 | montant_paiement=5476.03000000 | fk_bank=8 | somme_imputee=5476.03000000 | nb_factures=1 rowid=23 | ref=PAY2510-0023 | datep=2025-10-23 12:00:00 | montant_paiement=8884.06000000 | fk_bank=24 | somme_imputee=8884.06000000 | nb_factures=6 (2 lignes) ===== 12. Paiements dont le montant differe de la somme imputee ===== SELECT p.rowid,p.ref,p.datep,p.amount,COALESCE(SUM(pf.amount),0) AS impute,ROUND(p.amount-COALESCE(SUM(pf.amount),0),2) AS ecart FROM llx_paiement p LEFT JOIN llx_paiement_facture pf ON pf.fk_paiement=p.rowid GROUP BY p.rowid,p.ref,p.datep,p.amount HAVING ABS(p.amount-COALESCE(SUM(pf.amount),0))>0.001 ORDER BY ABS(p.amount-COALESCE(SUM(pf.amount),0)) DESC ----- (0 lignes) FIN