Flat Zoho Books invoice view in Zoho Analytics with "Skonto" and "Storno" detection (cash discount/cancellation)

Flat Zoho Books invoice view in Zoho Analytics with "Skonto" and "Storno" detection (cash discount/cancellation)

if you are trying to compile and analyze Zoho Finance invoice data in Zoho Analtics, we at CEYOND are sure this SQL query snippet can help you.

It will collate the invoices, payments, credit notes and sales. It is important that the tables from Zoho Finance and Zoho CRM are in the same workspace.

For the European and German speaking countries the view also contains two special features:
- Skonto (cash discount) detection based on a discount of 3%.
- Storno (invoice cancellation) detection based on existing 100% credit notes

We hope it helps you!
  2. SELECT
  3. i."Invoice Date" "Invoice Date",
  4. i."Invoice Number" "Invoice Number",
  5. c."Customer Name" "Customer Name",
  6. d."Deal Name" "Deal Name",
  7. if(i."Sub Total (BCY)"  = i."Write Off Amount (BCY)", 'Write Off', if(i."Sub Total (BCY)"  = ic."CreditNotes Sub (BCY)", 'Cancel', i."Invoice Status")) "Invoice Status",
  8. i."Sub Total (BCY)" "Sub Total (BCY)",
  9. i."Total (BCY)" "Total (BCY)",
  10. i."Discount (%)" "Discount (%)",
  11. i."Leistungszeitraum" "Leistungszeitraum",
  12. i."Purchase Order#" "Purchase Order#",
  13. concat('https://books.zoho.eu/app/200XXXXX#/invoices/', i."Invoice ID") "Invoice Link",
  14. concat('https://crm.zoho.eu/crm/orgXXXXXXX/tab/Potentials/', i."CRM Potential ID") "Deal Link",
  15. if(i."Discount (%)"  = 3, i."Discount Amount", 0) "Skonto Amount",
  16. if(i."Discount (%)"  = 3, 0, if(i."Discount Amount"  = 0, i."Discount Amount (BCY)", i."Discount Amount")) "Rabatt Amount",
  17. i."Write Off Amount (BCY)" "Write Off Amount (BCY)",
  18. i."Balance (BCY)" "Balance (BCY)",
  19. (i."Total (BCY)" -p."Amount (BCY)") "Payment Diff",
  20. p."Amount (BCY)" "Payment Amount",
  21. i."Type" "Type",
  22. i."Source" "Source",
  23. i."Due Date" "Due Date",
  24. i."Expected Payment Date" "Expected Payment Date",
  25. i."Last Payment Date" "Last Payment Date",
  26. days_between(i."Last Payment Date", i."Due Date") "Payment Due Days",
  27. i."Payment Terms" "Payment Terms",
  28. i."Notes" "Notes",
  29. i."Terms & Conditions" "Terms & Conditions",
  30. a."Billing Street" "Billing Street",
  31. a."Billing City" "Billing City",
  32. a."Billing Code" "Billing Code",
  33. a."Billing State" "Billing State",
  34. a."Billing Country" "Billing Country",
  35. d."Deal Owner Name" "Deal Owner",
  36. s."Name" "Sales Person",
  37. d."Amount" "Deal Amount",
  38. d."Closing Date" "Deal Closing Date",
  39. i."Created Time" "Created Time",
  40. cr."UserName" "CreatedUser",
  41. i."Last Modified Time" "Last Modified Time",
  42. mo."UserName" "ModifiedUser",
  43. i."Invoice ID" "Invoice ID",
  44. i."CRM Potential ID" "CRM Potential ID"
  45. FROM  Invoices i
  46. LEFT JOIN Customers c ON (c."Customer ID"  = i."Customer ID") 
  47. LEFT JOIN "Deals (Zoho CRM)" d ON (d."Id"  = i."CRM Potential ID") 
  48. LEFT JOIN "Sales Persons" s ON (s."Sales Person ID"  = i."Sales Person ID") 
  49. LEFT JOIN "Price lists" pr ON (pr."PriceList ID"  = i."PriceList ID") 
  50. LEFT JOIN "Subscriptions" su ON (su."Subscription ID"  = i."Subscription ID") 
  51. LEFT JOIN "Customer Addresses" a ON (a."Address ID"  = i."Address ID") 
  52. LEFT JOIN "Users" cr ON (i."Created By"  = cr."User ID") 
  53. LEFT JOIN "Users" mo ON (i."Modified By"  = mo."User ID") 
  54. LEFT JOIN( SELECT
  55. ip."Invoice ID" "Invoice ID",
  56. sum(ip."Amount (BCY)") "Amount (BCY)"
  57. FROM  "Invoice Payments" ip 
  58. GROUP BY  "Invoice ID" 
  59. ) p ON (p."Invoice ID"  = i."Invoice ID") 
  60. LEFT JOIN( SELECT
  61. ii."Invoice ID" "Invoice ID",
  62. sum(cn."Sub Total (BCY)") "CreditNotes Sub (BCY)",
  63. sum(cn."Total (BCY)") "CreditNotes Total (BCY)",
  64. count(1) "CreditNotes Count",
  65. min(cn."Credit Note Date") "CreditNotes Date First",
  66. max(cn."Credit Note Date") "CreditNotes Date Last"
  67. FROM  "Creditnotes Invoice" ii
  68. LEFT JOIN "Credit Notes" cn ON (ii."CreditNotes ID"  = cn."CreditNotes ID")  
  69. GROUP BY  ii."Invoice ID" 
  70. ) ic ON (ic."Invoice ID"  = i."Invoice ID")