Overdue payment notice RPA

Period 2025.06 — 2025.07

At the start of each month, the planning team sent email and AlimTalk notices to overdue customers, typing into Excel and making PDFs by hand. I took on an RPA project, from planning onward, to move that work onto one page.

Overdue notice work reduced from over 2 days to 10 minutes

Team: Solo
Role: PlanningDB and flow designPDF generation automationMail and AlimTalk notice automationDesignClient page

Java 17Spring Boot 3.5MyBatisMySQLpoi‑ooxmlLibreOfficeJavaMailSenderNext.js 15React 19

Overdue customer lookup, receivable registration and deletion on one page
PDFs generated per customer with their overdue amount and due date
Email and AlimTalk notices sent with one button

A different PDF and mail body for every customer

Problem: Each month, the different amounts and due dates of more than 100 overdue customers were typed into an Excel template, exported to PDF and written into each mail body by hand.
Solution: Mapped the template cells that change (document number, issue date, recipient, billing month, overdue amount and due date) to constants. poi‑ooxml fills those cells with DB values, and LibreOffice turns the file into a PDF. The mail subject and body are filled in per customer, and JavaMailSender sends them with the PDF attached. Overdue notices that took over 2 days now take 10 minutes.

PDF conversion on a server without install rights

Problem: Aspose was the first choice for converting Excel to PDF, but its free license stamped red text at the top of every PDF. I also had no permission to install software on the production server.
Solution: Switched to LibreOffice and confirmed the conversion worked. Instead of installing it, I uploaded it as an AppImage and started the conversion process from the WAS with the headless option. Korean text in the PDFs came out broken, so I also uploaded the Malgun Gothic font, which the server lacked.

Regenerating the same document

Problem: Requesting a document again under the same conditions could create the same file again and waste resources. Because these were IDC servers without S3, files had to be stored on the server's disk.
Solution: Built a 16‑character hash from the customer ID, issue date, billing month, due date and overdue amount. A document log table uses that hash and the file extension as its composite key, and files are stored in a directory per hash. Documents already in the log are reused, and new ones are created only when missing.

Progress during a bulk send

Problem: The screen did not show progress when sending notices to many customers at once.
Solution: Stored logs for each sent item and streamed progress events over SSE. Email and AlimTalk sending ran in parallel with 4 core threads and a maximum of 8.